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-2014    11   SATURDAY 
16-NOV-2014    11   SUNDAY   
17-NOV-2014    12   MONDAY   
18-NOV-2014    13   TUESDAY  
19-NOV-2014    14   WEDNESDAY
20-NOV-2014    15   THURSDAY 
21-NOV-2014    16   FRIDAY   
22-NOV-2014    16   SATURDAY 
23-NOV-2014    16   SUNDAY   
24-NOV-2014    17   MONDAY   
25-NOV-2014    18   TUESDAY  
26-NOV-2014    19   WEDNESDAY
27-NOV-2014    20   THURSDAY 
28-NOV-2014    21   FRIDAY   
29-NOV-2014    21   SATURDAY 
30-NOV-2014    21   SUNDAY   
01-DEC-2014    1   MONDAY   
02-DEC-2014    2   TUESDAY  
03-DEC-2014    3   WEDNESDAY
04-DEC-2014    4   THURSDAY 
05-DEC-2014    5   FRIDAY   
06-DEC-2014    5   SATURDAY 
07-DEC-2014    5   SUNDAY   
08-DEC-2014    6   MONDAY   
09-DEC-2014    7   TUESDAY  
10-DEC-2014    8   WEDNESDAY
11-DEC-2014    9   THURSDAY 
12-DEC-2014    10   FRIDAY   
13-DEC-2014    10   SATURDAY 
14-DEC-2014    10   SUNDAY   
15-DEC-2014    11   MONDAY   
16-DEC-2014    12   TUESDAY  
17-DEC-2014    13   WEDNESDAY
18-DEC-2014    14   THURSDAY 
19-DEC-2014    15   FRIDAY   
20-DEC-2014    15   SATURDAY 
21-DEC-2014    15   SUNDAY   
22-DEC-2014    16   MONDAY   
23-DEC-2014    17   TUESDAY  
24-DEC-2014    18   WEDNESDAY
25-DEC-2014    19   THURSDAY 
26-DEC-2014    20   FRIDAY   
27-DEC-2014    20   SATURDAY 
28-DEC-2014    20   SUNDAY   
29-DEC-2014    21   MONDAY   
30-DEC-2014    22   TUESDAY  
31-DEC-2014    23   WEDNESDAY





Other than style changes (this was a really quick, one-time-only thing so sloppy doesn't truly matter, no one will ever see it but me and you guys - function does always matter)... 

Is this approach the best (only?) one for this kind of operation in Oracle?
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 &lt;= 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
  SET    t.business_day_number = NVL(u.new_business_day_number,0);


I put the NVL function to avoid null value when the month happens to start in a weekend (feb-mar/2015 for instance) but I could have used the "ELSE 0" clause in the CASE expression as well.

I hope this helped you in some way.