Q

Running a Monday-Friday report to include weekend data

SQL expert Rudy Limeback explains how to run 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?

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

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close