Counting business days in a month, ignoring holidays

This table simply contains date information. I went ahead and used what I currently have since this is a one-time operation. However, since I only recently started working with Oracle, I would like to learn about all its different features and such.

The code is trying to establish if it is a business day or not while ignoring holidays. Essentially, we want a running total of business days for each day in a month. This value does need to be stored in the main table in the end.

Is there a better way to write this?

DECLARE
    Cursor MyCursor IS
        select  cc.calendar_dt,
                cc.CALENDAR_DT_ID,  
                calendar_month_abbreviation, 
                calendar_year_number,
                to_char(cc.calendar_dt,'MM') as m
        from    CALENDAR_DT cc 
        order   by cc.calendar_dt ;

    entry MyCursor%ROWTYPE;

    new_number NUMBER := 1;
    curr_month NUMBER := 0;   
begin  

    FOR entry IN MyCursor LOOP            

        if (curr_month != (entry.m)) then 
            new_number := 1; 
            curr_month := entry.m; 
        else 
            new_number := new_number + (case when to_char(entry.CALENDAR_DT,'D') in (1,7) then 0 else 1 end);
        end if;

        DBMS_OUTPUT.PUT_LINE(entry.calendar_dt || '    ' || new_number || '   ' || to_char(entry.CALENDAR_DT,'DAY') );

        --UPDATE CALENDAR_DT
        --        SET  business_day_number = new_number
        --        WHERE  CALENDAR_DT_ID = ENTRY.CALENDAR_DT_ID;

    END LOOP;

END;

Output with year=2014 and month in nov, dec and it is what I expect:

`01-NOV-2014 1 SATURDAY
02-NOV-2014 1 SUNDAY
03-NOV-2014 2 MONDAY
04-NOV-2014 3 TUESDAY
05-NOV-2014 4 WEDNESDAY
06-NOV-2014 5 THURSDAY
07-NOV-2014 6 FRIDAY
08-NOV-2014 6 SATURDAY
09-NOV-2014 6 SUNDAY
10-NOV-2014 7 MONDAY
11-NOV-2014 8 TUESDAY
12-NOV-2014 9 WEDNESDAY
13-NOV-2014 10 THURSDAY
14-NOV-2014 11 FRIDAY
15-NOV

It is possible to do a cumulative sum in Oracle, indeed, using Analytic Function.

I've created a sample test table trying to reproduce the model presented in the question:

CREATE TABLE CALENDAR_DT AS
SELECT rownum calendar_dt_id
      ,DATE '2014-01-01'   + rownum - 1 calendar_dt
      ,0      business_day_number
FROM dual
CONNECT BY LEVEL <= 365;

A cumulative sum can be achieved using this sintax for instance:

SELECT SUM(t.calendar_dt_id) over(ORDER BY t.calendar_dt_id) cumulative_sum
      ,t.calendar_dt_id
FROM calendar_dt t;

The resultset will look like this

CUMULATIVE_SUM CALENDAR_DT_ID
-------------- --------------
         1              1
         3              2
         6              3
        10              4
        15              5
        21              6
        28              7
        36              8
        45              9
        55             10
        66             11
        78             12
        91             13
       105             14
       120             15
       136             16
       153             17
       171             18
       190             19
       210             20

The order by clause inside the parenthesis produces de cumulative sum.
If it is necessary to reset the sum every new month you can use the partition clause:

SELECT SUM(t.calendar_dt_id) over(PARTITION BY to_char(t.calendar_dt,'MM') ORDER BY t.calendar_dt_id) cumulative_sum
      ,t.calendar_dt
      ,t.calendar_dt_id
FROM calendar_dt t;

CUMULATIVE_SUM CALENDAR_DT CALENDAR_DT_ID
-------------- ----------- --------------
         1 1/1/2014                 1
         3 2/1/2014                 2
         6 3/1/2014                 3
        10 4/1/2014                 4
        15 5/1/2014                 5
        21 6/1/2014                 6
        28 7/1/2014                 7
        36 8/1/2014                 8
        45 9/1/2014                 9
        55 10/1/2014               10
        66 11/1/2014               11
        78 12/1/2014               12
        91 13/1/2014               13
       105 14/1/2014               14
       120 15/1/2014               15
       136 16/1/2014               16
       153 17/1/2014               17
       171 18/1/2014               18
       190 19/1/2014               19
       210 20/1/2014               20
       231 21/1/2014               21
       253 22/1/2014               22
       276 23/1/2014               23
       300 24/1/2014               24
       325 25/1/2014               25
       351 26/1/2014               26
       378 27/1/2014               27
       406 28/1/2014               28
       435 29/1/2014               29
       465 30/1/2014               30
       496 31/1/2014               31
        32 1/2/2014                32
        65 2/2/2014                33
        99 3/2/2014                34
       134 4/2/2014                35
       170 5/2/2014                36
       207 6/2/2014                37
       245 7/2/2014                38
       284 8/2/2014                39
       324 9/2/2014                40
       365 10/2/2014               41
       407 11/2/2014               42
       450 12/2/2014               43
       494 13/2/2014               44
       539 14/2/2014               45
       585 15/2/2014               46
       632 16/2/2014               47
       680 17/2/2014               48
       729 18/2/2014               49
       779 19/2/2014               50
       830 20/2/2014               51
       882 21/2/2014               52
       935 22/2/2014               53
       989 23/2/2014               54
      1044 24/2/2014               55
      1100 25/2/2014               56
      1157 26/2/2014               57
      1215 27/2/2014               58
      1274 28/2/2014               59
        60 1/3/2014                60

In those examples I used the id for summing just to get more evident. In your case you can use a sum of 1 conditioned to be a "day of week" using a Case expression exactly the way you used in your block, like this:

SELECT SUM(CASE
             WHEN to_char(t.calendar_dt, 'd') NOT IN (1, 7) THEN
               1
           END)
        over(PARTITION BY to_char(t.calendar_dt, 'MM')
             ORDER BY t.calendar_dt) new_business_day_number
      ,t.calendar_dt_id
      ,t.calendar_dt
      ,t.business_day_number
FROM calendar_dt t

After achieving the expected resultset, you can proceed with the update, but you don't need a block to do this if you use a MERGE statement:

```
MERGE INTO calendar_dt t
USING (SELECT SUM(CASE
WHEN to_char(t.calendar_dt, 'd') NOT IN (1, 7) THEN
1
END)
over(PARTITION BY to_char(t.calendar_dt, 'MM')
ORDER BY t.calendar_dt) new_business_day_number
,t.rowid rid
FROM calendar_dt t) u
ON (u.rid = t.rowid)
WHEN MATCHED THEN
UPDATE

StackExchange Code Review Q#68912, answer score: 3