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.
This was first published in January 2008