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?