Ask the Expert

Running a Monday-Friday report to include weekend data

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: