As part of my current role as a tester I am often required to write the same basic expressions again and again in my queries, most noticeably to bring back data for specific date ranges, therefore for the benefit of anyone else out there who has to do likewise I have detailed below my method for returning the following basic ranges:
Daily; Week to date; Month to date; Year to Date; and Rolling year (or Last 12 months). Presently I am working predominantly with Oracle based systems, there for the following is only the PL SQL for the aforementioned ranges.
Daily
As good a starting place as any, the query to return data for the day is the basis from which we can build the other expressions;
1 Select a_field
2 from a_table
3 where the_date = to_date('01/01/2006', 'DD/MM/YYYY')
This will return the data in a_field for the 1st January 2006 only, however to make the query more flexible we can replace the exact data with a variable like '&date' which will prompt the user to enter the date at run time.
The format in the second half of the to_date expression is the format that the date must be entered in and also tells the SQL engine what format the date is being entered in, therefore you can set the format however you like, for example 'MM/DD/YYYY' for the US date format.
One final trick before we get into the other ranges is to account for dates outside of the range for which we have data. This may be required for example when the data load into a data warehouse is a day behind the current date (as data loads often run over night up to and including that day’s data, therefore in the morning the data is current up to the day before). This method will also be used to account for partial ranges in the subsequent queries; e.g. a partial month or week.
1 Select a_field
2 from a_table
3 where the_date = (case when to_date('&Date', 'DD/MM/YYYY') > trunc(sysdate)-1
4 then trunc(sysdate)-1
5 else to_date('&Date', 'DD/MM/YYYY')
6 end)
The case statement here evaluates the date entered by the user to determine if it is greater than the most recent date for which we have data – in this case the day before today (sysdate is Oracle’s command to return the current date). Note that we truncate sysdate because it is a date/time entry, but we are only interested in the date portion.
Weekly
For week ending we can make use of the default behaviour for subtracting days from a date
1 Select a_field
2 from a_table
3 where the_date between (to_date('&Date', 'DD/MM/YYYY')-6)
4 and (case when to_date('&Date', 'DD/MM/YYYY') > trunc(sysdate)-1
5 then trunc(sysdate)-1
6 else to_date('&Date', 'DD/MM/YYYY')
7 end)
Note that in this example I have used the date function ‘between… and’, however some people prefer to use a pair of ‘where’ clauses, as in the example below.
1 where the_date >= (to_date('&Date', 'DD/MM/YYYY')-6)
2 and the_date <= (case when to_date('&Date', 'DD/MM/YYYY') > trunc(sysdate)-1
3 then trunc(sysdate)-1
4 else to_date('&Date', 'DD/MM/YYYY')
5 end)
As far as I am aware, there is no performance difference between the two, more a matter of personal preference. Some people may feel that the where statements give them more control over exactly which dates are included because they can make use of ‘<, <=, =, >=, >, etc’ and may be uncertain of whether ‘between… and’ is inclusive of the dates or not. I am confident enough to say however that it is inclusive therefore the same amount of control can be attained by varying the date used, e.g. shift it one day higher to emulate ‘>’, etc.
This sql relies on the user entering the week ending date for the week they are interested in, however if you wanted to construct it so that all they had to do was enter a date within the week of interest, you would need to evaluate the date to identify the begining and end of the week. One way this could be done is to use the next_day function to identify for example the next Saturday or Sunday, depending when you define the week as ending. By subtracting 7 from the date then and finding the next Sunday or Monday (depending when you define the week as starting, you would have your week begining date too.
Monthly
For month ending:
1 Select a_field
2 from a_table
3 where the_date >= to_date('&Date','MM YYYY')
4 and the_date <= (case when last_day(to_date('&Date','MM YYYY')) > trunc(sysdate)-1
5 then trunc(sysdate)-1
6 else last_day(to_date('&Date','MM YYYY'))
7 end)
Notice I have used a different date format here - 'MM YYYY', which allows me to enter the date as just a ‘month year’ combination, such as ‘January 2006’. The to_date function automatically takes this as the first of the month allowing me to use the last_day function to acquire the end of the month. Again the case statement allows data for a partial month to be retrieved.
If you wanted to create a specific month to date query for a particular day in the month such as month to the 15/01/2006 all you need do is change the date format, such as to 'DD/MM/YYYY' in this example.
Yearly
For the year to date:
1 Select a_field
2 from a_table
3 where the_date >= TRUNC(to_date('&date', 'MM YYYY'), 'YYYY')
4 and the_date <= (case when last_day(to_date('&Date','MM YYYY')) > trunc(sysdate)-1
5 then trunc(sysdate)-1
6 else last_day(to_date('&Date','MM YYYY'))
7 end)
The paramater '&date' is entered as a month year combination (e.g. January 2006), but to_date converts it to ‘DD/MM/YYYY’. By truncating the date on the year, the date is converted to ‘01-01-YYYY.’
Once again if you required a year to a specific date then simply apply the appropriate format ('DD/MM/YYYY') to the expression. Alternatively, you may just want to enter the year and rely on the case statement to find the appropriate point for year to date, e.g.:
1 where the_date >= TRUNC(to_date('&date', 'YYYY'), 'YYYY')
2 and the_date <= (case when TRUNC(to_date('&date'+1, 'YYYY'), 'YYYY')-1 > trunc(sysdate)-1
3 then trunc(sysdate)-1
4 else TRUNC(to_date('&date'+1, 'YYYY'), 'YYYY')-1
5 end)
Last 12 Months
For the last 12 months to date you can utilise any of the date formating options shown above to define the date at which the range runs from and to. In the example I provide below I am interested in the range from the begining of the month to the end of the month 12 months later:
1 Select a_field
2 from a_table
3 where the_date >= add_months(to_date('&date', 'MM YYYY'),-11)
4 and the_date <= (case when last_day(to_date('&date', 'MM YYYY')) > trunc(sysdate)-1
5 then trunc(sysdate)-1
6 else last_day(to_date('&date', 'MM YYYY'))
7 end)
On occasion I have also had the need for a quarter to date range, but at the moment I can't find an example of this, so I will endeavour to provide this at some point, unless anyone else would like to provide it. Since I am often also required to query against both Oracle systems and SQL server, I aim to at some point provide each of these ranges in T-SQL too. But that's for another blog.
James