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
The following query will do the required:
, (SELECT MAX(WEEKDATE) AS MAX_WEEKDATE FROM TESTTABLE) B
WEEKDATE BETWEEN ADD_MONTHS(MAX_WEEKDATE,-6) + 1 AND MAX_WEEKDATE;
In case of issues please let me know.