Query to Filter latest 1 year data

Database

Query to Filter latest 1 year data

Hi Everyone,

I have a column 'weekDate' (date Datatype) in my table and the table has data from 10-01-2013 to 12-31-2014. The table gets refreshed every week so it keeps on adding one week data.

In the report I have to show data from max(weekDate) to 6 months early to that. so the report should show data from '07-01-2014' to '12-31-2014'. the next week after refresh the report should show data from '07-07-2014' to '01-07-2015'.

I tried using add_months and extract(month from weekDate) so I couldnot able to figure out 

sel * from testtable where weekDate between (weekDate -180) and weekDate.

Please someone suggest a way to do this

1 REPLY

Re: Query to Filter latest 1 year data

Hi SarranyaNithya,

The following query will do the required:

SELECT
A.*
FROM
TESTTABLE A
, (SELECT MAX(WEEKDATE) AS MAX_WEEKDATE FROM TESTTABLE) B
WHERE
WEEKDATE BETWEEN ADD_MONTHS(MAX_WEEKDATE,-6) + 1 AND MAX_WEEKDATE;

In case of issues please let me know.


Thanks,

Rohan Sawant