SQL – Running Sum, Skipping Weekends

8 12 2009

December 7, 2009

Some time ago the following question appeared on the comp.databases.oracle.misc Usenet group: http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/cad8621182ae7d1b

This sounds like it should be super simple, but I can’t think how to do it using plain SQL.

I am going to display some rows, and I want to number them in the display, like using rownum, except that the number only goes up when the row has some property (I don’t care if it displays or not when it doesn’t go up).

To explain, imagine

select rownum  , the_date , to_char(dates.the_date,'DY') DAY 
from my_table 
order by the_date

shows 
        1       1-Jan-2008      TUE 
        2       2-Jan-2008      WED 
        3       3-Jan-2008      THU 
        4       4-Jan-2008      FRI 
        5       5-Jan-2008      SAT 
        6       6-Jan-2008      SUN 
        7       7-Jan-2008      MON 
        8       8-Jan-2008      TUE

but I don’t want to count the weekend.  what I want to show would be the following instead

        1       1-Jan-2008      TUE 
        2       2-Jan-2008      WED 
        3       3-Jan-2008      THU 
        4       4-Jan-2008      FRI 
                5-Jan-2008      SAT 
                6-Jan-2008      SUN 
        5       7-Jan-2008      MON 
        6       8-Jan-2008      TUE

Looks simple, but how to do this in plain SQL?

I provided the following solution:

Do it with the SUM analytical function and DECODE:

SELECT 
  TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, 
  TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY 
FROM 
  DUAL 
CONNECT BY 
  LEVEL<=20; 
 
THE_DATE  DAY 
--------- --- 
01-JAN-08 TUE 
02-JAN-08 WED 
03-JAN-08 THU 
04-JAN-08 FRI 
... 
19-JAN-08 SAT 
20-JAN-08 SUN

Step 1:

SELECT 
  SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE) MY_COUNT, 
  THE_DATE, 
  DAY 
FROM 
  (SELECT 
    TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, 
    TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY 
  FROM 
    DUAL 
  CONNECT BY 
    LEVEL<=20); 
 
  MY_COUNT THE_DATE  DAY 
---------- --------- --- 
         1 01-JAN-08 TUE 
         2 02-JAN-08 WED 
         3 03-JAN-08 THU 
         4 04-JAN-08 FRI 
         4 05-JAN-08 SAT 
         4 06-JAN-08 SUN 
         5 07-JAN-08 MON 
         6 08-JAN-08 TUE 
         7 09-JAN-08 WED 
         8 10-JAN-08 THU 
         9 11-JAN-08 FRI 
         9 12-JAN-08 SAT 
         9 13-JAN-08 SUN 
        10 14-JAN-08 MON 
        11 15-JAN-08 TUE 
        12 16-JAN-08 WED 
        13 17-JAN-08 THU 
        14 18-JAN-08 FRI 
        14 19-JAN-08 SAT 
        14 20-JAN-08 SUN

Now to remove the numbers that should not print, using a second DECODE:

SELECT 
  DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE)) MY_COUNT, 
  THE_DATE, 
  DAY 
FROM 
  (SELECT 
    TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, 
    TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY 
  FROM 
    DUAL 
  CONNECT BY 
    LEVEL<=20); 
 
MY THE_DATE  DAY 
-- --------- --- 
1  01-JAN-08 TUE 
2  02-JAN-08 WED 
3  03-JAN-08 THU 
4  04-JAN-08 FRI 
   05-JAN-08 SAT 
   06-JAN-08 SUN 
5  07-JAN-08 MON 
6  08-JAN-08 TUE 
7  09-JAN-08 WED 
8  10-JAN-08 THU 
9  11-JAN-08 FRI 
   12-JAN-08 SAT 
   13-JAN-08 SUN 
10 14-JAN-08 MON 
11 15-JAN-08 TUE 
12 16-JAN-08 WED 
13 17-JAN-08 THU 
14 18-JAN-08 FRI 
   19-JAN-08 SAT 
   20-JAN-08 SUN

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko provided the following solution:

SQL> alter session set nls_territory='AMERICA'; 
 
Session altered. 
 
SQL> alter session set nls_date_language='AMERICAN'; 
 
Session altered. 
 
SQL> 
SQL> with my_table as ( 
   2    select date '2008-01-01' + rownum -1 the_date 
   3    from dual connect by level <=10 
   4  ) 
   5  select 
   6  case 
   7  when 
   8  to_char(the_date,'d') not in (1,7) 
   9  then row_number() 
  10  over(partition by case when to_char(the_date,'d') not in (1,7) then 1 
  11  end order by the_date) 
  12  end row_num, 
  13  the_date, 
  14  to_char(the_date,'DY') DAY 
  15  from my_table 
  16  order by the_date; 
 
    ROW_NUM THE_DATE           DAY 
---------- ------------------ ------------ 
          1 01-JAN-08          TUE 
          2 02-JAN-08          WED 
          3 03-JAN-08          THU 
          4 04-JAN-08          FRI 
            05-JAN-08          SAT 
            06-JAN-08          SUN 
          5 07-JAN-08          MON 
          6 08-JAN-08          TUE 
          7 09-JAN-08          WED 
          8 10-JAN-08          THU

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  
Ken Denny provided the following solution:

select r_num, the_date, to_char(dates.the_date,'DY') DAY 
  from (select rownum r_num, the_date 
          from (select the_date from my_table 
                  where to_char(dates.the_date,'DY') IN 
                     ('MON','TUE','WED','THU','FRI') 
                  order by the_date) 
        union 
        select null r_num, the_date from my_table 
          where to_char(dates.the_date,'DY') IN ('SAT','SUN')) 
   order by the_date;

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: