EXPERT RESPONSE
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.
|
