I have below query which i am using for reporting in Cognos. I am doing reporting every week on Monday for the previous week from Monday till Sunday.
Currently this date is hardcoded but i want to make it dynamic so that everytime when i run this report on Monday i dont have to change the date for previous week.
Is it possible i can make DAY_DATE dynamic using something like sysdate in select query ?
select ID,NAME,DAY_DATE
from TEST_REPORT
WHERE DAY_DATE BETWEEN to_date ('20170904', 'YYYYMMDD') and to_date ('20170910', 'YYYYMMDD');
You can calculate start and end dates of previous week from the current date using TRUNC (date) function.
Let say you are running the query on monday 2017-09-11, then on friday 2017-09-15, and the query must always generate a report for previous week.
This query calculates a start date of the current week:
SELECT trunc( date '2017-09-11', 'IW' ) as x,
trunc( date '2017-09-15', 'IW' ) as y
from dual;
X Y
---------------- ----------------
2017/09/11 00:00 2017/09/11 00:00
To calculate a start date of the previous week, substract 1 day from the above dates, and use TRUCT again:
SELECT trunc( trunc( date '2017-09-11', 'IW' ) - 1, 'IW') as start_last_week,
trunc( trunc( date '2017-09-15', 'IW' ) - 1, 'IW') as start_last_week1
from dual;
START_LAST_WEEK START_LAST_WEEK1
---------------- ----------------
2017/09/04 00:00 2017/09/04 00:00
So, in your query just use this clause (date >= than a start of previous week and < that a start of current week):
WHERE DAY_DATE>= trunc( trunc( sysdate, 'IW' ) - 1, 'IW')
and DAY_DATE < trunc( sysdate, 'IW' )