Counting business days in a month, ignoring holidays

Question

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?

Itagyba Abondanza Kuhlmann · Accepted Answer · 2015-02-13 19:38:06Z

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
  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.

\$\begingroup\$ Cursor be gone! \$\endgroup\$
– RubberDuck
Feb 13, 2015 at 22:51 — RubberDuck, Feb 13, 2015 at 22:51

Phrancis · Accepted Answer · 2014-11-04 23:59:55Z

I was trying to think of a way to do this in a set based way, but given the nature of the cumulative sum that you need, I doubt that's possible. You could try using variables and CASE statements but that would amount to just about the same thing, if not worse. Oracle/SQL is likely not the best tool for this type of operation.

That said, here are some thoughts.

Aliases and naming

MyCursor is not a good name. Naming should describe what something does, not what type of thing it is. The type of thing should be easy to make out by looking at how the code is written. Something like CountBusinessDays or IncrementDaysCounter might work better.

           to_char(cc.calendar_dt,'MM') as m

Very short aliases (and variable names) can make a query become increasingly difficult to write and read as it grows bigger. As is often the case, real business queries are often quite complex, so I would recommend using aliases that at least say something about what it references. In this case, I think month or just mon or MM would have been perfectly fine.

Style

Your indentation is consistent and makes your code easy to understand, I commend you for that as SQL is not as instinctual as other types of programming languages to use helpful indentation.

However, your capitalization of SQL key words is not consistent at all, and that can make it more difficult to read. The standard is all caps, although it doesn't matter which one you use too much, as long as it is consistent. Look at your cursor declaration statement, for example:

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;

This would be easier on the eyes:

DECLARE
    CURSOR CountBusinessDays IS
        SELECT  cc.calendar_dt,
                cc.CALENDAR_DT_ID,  
                calendar_month_abbreviation, 
                calendar_year_number,
                TO_CHAR(cc.calendar_dt,'MM') AS month
        FROM    CALENDAR_DT cc 
        ORDER   BY cc.calendar_dt;
    entry MyCursor%ROWTYPE;
    new_number NUMBER := 1;
    curr_month NUMBER := 0;

Commented out code

You have this:

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

Is there a reason this is kept as part of the script? If it's not needed, it probably should just be deleted. That way when you look or the next maintainer looks at your query in a few months or years, there will not be ambiguity about why that is in the code.

I was testing with it commented out but at the time I posted the question I had not ran it fully. I considered removing it in the question but I left it in to mostly show how everything would eventually be applied. I wasn't sure if that would effect the way this problem should be approached or not. — gloomy.penguin, Nov 5, 2014 at 14:48
I was trying to use lag at first... but then it wouldn't let me use the actual lag column as the first argument in the lag. Also, since it's code that was only ran one time, I didn't see an issue with using names (like for the cursor) that were copied straight from the internet. — gloomy.penguin, Nov 5, 2014 at 14:50
Is a cursor really the only approach to handle this kind of situation in Oracle? — gloomy.penguin, Nov 5, 2014 at 14:51
I think not just Oracle, but any RDBMS. I'm mostly used to PosgreSQL and if I had something like this I would have to use a loop or cursor (they are the same thing, really), I can't think of any way of doing this that is idiomatic to a SQL database. Since you have to evaluate every record one at a time to evaluate whether or not to increment. — Phrancis, Nov 6, 2014 at 0:15

RubberDuck · Accepted Answer · 2015-02-13 23:02:02Z

I'd like to share something with you. Another reviewer kind of beat you up for leaving this commented out code.

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

I understand that you did this so you could preview results. By all means, leave it. However, you could change the way you comment it out to make it less of a hassle to "toggle". Consider using a multi-line comment like so.

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

Take note that I added a single line comment before the closing "bracket". Why did I do that? Watch.

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

With just two keystrokes I've re-enabled the update statement. The single line comment on the closing marker prevents it from becoming a syntax error.

I know it's nothing to do with your code or cursor, but this little trick has saved me cumulative hours, maybe days, over the years.

I was using Toad for this... so ctrl+b and ctrl+shift+b add/remove comment blocks. I really didn't mean to leave it commented in the question. It was more about concept and approach to the problem rather than the code itself. I come from SQL Server so I wasn't sure if there was a better way to write that in Oracle or not. — gloomy.penguin, Feb 14, 2015 at 1:20

Stack Exchange Network

Counting business days in a month, ignoring holidays

3 Answers 3

Aliases and naming

Style

Commented out code

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql
datetime
oracle
or ask your own question.

Hot Network Questions

Counting business days in a month, ignoring holidays

3 Answers 3

Aliases and naming

Style

Commented out code

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sqldatetimeoracle or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql
datetime
oracle
or ask your own question.