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?