I need to run a report Monday through Friday.
- The Monday report pulls data for the previous Friday.
- The Tuesday report pulls data for the previous Saturday to Monday.
- The Wednesday report pulls data for the previous Tuesday.
- The Thursday report pulls data for the previous Wednesday.
- The Friday report pulls data for the previous Thursday.
How can this be done?
My approach (subject, of course, to rigorous testing) would be something like this:
select ... from ... where sales_date in ( current_date - interval case dayofweek(current_date) when 2 /* mon */ then 3 when 3 /* tue */ then 1 when 4 /* wed */ then 1 when 5 /* thu */ then 1 when 6 /* fri */ then 1 else null end day , current_date - interval case dayofweek(current_date) when 3 /* tue */ then 2 else null end day , current_date - interval case dayofweek(current_date) when 3 /* tue */ then 3 else null end day )
The DAYOFWEEK function does not exist with that actual name in all database systems, so have a look and see what functions are available to you. The important part of the approach is the use of the IN list, containing up to 3 dates which your column will be compared to.
Let's look at the first CASE expression. On Monday, subtracting 3 days yields the Friday before, and on Tuesday through Friday, subtracting 1 day yields the respective day before. That's the first date in the IN list. The other two dates in the IN list will be NULL except on Tuesday when the second date will be the Sunday before, and the third date will be the Saturday before.
Why NULL? Because the subtraction CURRENT_DATE - INTERVAL n DAY will return NULL if n is NULL. Any calculation involving NULL returns NULL (NULL can be called "greedy" in this regard). Thus the IN list will resolve to something like:
where sales_date in ( '2008-02-29' , NULL , NULL )
Note that if you accidentally run the report on Saturday or Sunday, where the day of the week is 7 or 1, all three CASE expressions return NULL, and no data will be extracted at all.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading