# 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
--        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
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')
,t.calendar_dt_id
,t.calendar_dt
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')
,t.rowid rid
FROM calendar_dt t) u
ON    (u.rid = t.rowid)
WHEN  MATCHED THEN
UPDATE


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.

• Cursor be gone! – RubberDuck Feb 13 '15 at 22:51

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
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
--        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 '14 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 '14 at 14:50
• Is a cursor really the only approach to handle this kind of situation in Oracle? – gloomy.penguin Nov 5 '14 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 '14 at 0:15

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

    --UPDATE CALENDAR_DT
--        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
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

• 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 '15 at 1:20