It is often convenient to set a number of session variables to capture date values that you use repeatedly in your queries. For example, if you have weeks that end on Saturday, you might want to have the date of the most recent Saturday in a session variable, called perhaps PREVIOUSSATURDAY. You can then use that session variable as the default date value in your queries – for example, “Periods”.”Date” = VALUEOF(NQ_SESSION.PREVIOUSSATURDAY).
Assume today is June 10. Using the convention that weeks begin on Sunday (adjust accordingly if that’s not the case for your enterprise), we can think of Current, Previous, and Next weeks.
Since you are going to be setting these date variables using physical SQL in initialization blocks, the SQL issued will be specific to the database platform you are using. For Oracle, you could write:
select
trunc(sysdate) - to_char(sysdate,'D')+1 CurrentSunday
, trunc(sysdate) - to_char(sysdate,'D')+2 CurrentMonday
, trunc(sysdate) - to_char(sysdate, 'D')+7 CurrentSaturday
, trunc(sysdate) - to_char(sysdate,'D')+8 NextSunday
, trunc(sysdate) - to_char(sysdate,'D') PreviousSaturday
, trunc(sysdate) - to_char(sysdate,'D')+2-8 PreviousSunday
, trunc(sysdate) - to_char(sysdate,'D')+2-7 PreviousMonday
, cast(to_char(trunc(sysdate), 'YYYY') as INT) CurrentYear
, Cast(to_char(trunc(sysdate), 'YYYY')-1 as INT) PreviousYear
, add_months(trunc(last_day(sysdate)),-1) + 1 CurrentMonthFirstDay
, last_day(trunc(sysdate)) CurrentMonthLastDay
, add_months(TRUNC(last_day(sysdate)),-2) + 1 PreviousMonthFirstDay
, case when last_day(SYSDATE) = SYSDATE then TRUNC(SYSDATE) else add_months(TRUNC(last_day(sysdate)),-1) end LASTDAYCOMPLETEMONTH
from dual;
If you are using a calendar that’s different from the normal “Gregorian” calendar (i.e. a fiscal calendar) that you have stored in a Periods table, you can write the analogous SQL for that calendar. You won't be able to use the Oracle date functions for many of the values you want, but you can still write the SQL to return the values according to the fiscal periods in your calendar using different methods.
