10 Replies Latest reply on Nov 23, 2012 4:53 AM by rp0428

# Holiday calendar table logic required in sql instead of pl sql

My function fn_test calculates the date-1. And if it's a holiday according to the table temp_calendar, then it recursively calls the fn_test again to do -1. This happens till I get a non holiday date.
I have implemented as follows:

But can I have a single SQL do it which is more efficient?

Drop table temp_calendar;
Create table temp_calendar(
id number,
holiday date);

Insert into temp_calendar values (1, '5-Jan-2012');
Insert into temp_calendar values (1, '6-Jan-2012');
Insert into temp_calendar values (1, '10-Jan-2012');
Insert into temp_calendar values (1, '2-Feb-2012');
Insert into temp_calendar values (1, '11-Feb-2012');
Commit;

CREATE OR REPLACE FUNCTION fn_test (in_date IN DATE)
RETURN DATE
IS
v_pr_day DATE;
BEGIN
v_pr_day := in_date-1;
FOR Calendar_Dates IN (SELECT holiday FROM temp_calendar)
LOOP
IF (v_pr_day = Calendar_Dates.holiday)
THEN
v_pr_day := fn_test (v_pr_day);
END IF;
END LOOP;
RETURN TRUNC (v_pr_day);
END fn_test;
/

Select fn_test('8-JAN-2012') from dual; -- Returns 7 Jan as no holiday in table temp_calendar.
But Select fn_test('7-JAN-2012') from dual; --Returns 4 Jan as 6 and 5 are holidays in table temp_calendar.

Thanks..
• ###### 1. Re: Holiday calendar table logic required in sql instead of pl sql
There are several methods available

Assuming you wont have 30 consecutive holidays
``````with t as
(
select to_date('8-JAN-2012','dd-MON-yyyy')-level dt
from dual
connect by level <= 30)
select max(dt) dt
from t
where dt not in (select holiday from temp_calendar);``````
Or you can use recursion..

Edited by: jeneesh on Nov 22, 2012 1:06 PM
1 person found this helpful
• ###### 2. Re: Holiday calendar table logic required in sql instead of pl sql
Hi Jeenesh,

Thanks for the answer.
But do you think that recursion is not the right thing to do here in terms of performance or in terms of good coding.

Thanks..
• ###### 3. Re: Holiday calendar table logic required in sql instead of pl sql
Hi,

Jeenesh's method is working with one SQL statement.
Recursion using CONNECT BY for such a small number should not affect performances.
It is anyway faster than using a PL/SQL block (a function in your case).

Regards.
Al
1 person found this helpful
• ###### 4. Re: Holiday calendar table logic required in sql instead of pl sql
The solution given by Jeneesh is mostly likely quite efficient. It will probably be very small improvements to performance to try recursive sql.

I would just like to point out one very bad piece of code in your function, however:
user8941550 wrote:
FOR Calendar_Dates IN (SELECT holiday FROM temp_calendar)
LOOP
IF (v_pr_day = Calendar_Dates.holiday)
THEN
v_pr_day := fn_test (v_pr_day);
END IF;
END LOOP;
Why oh why are you looping over all the holidays and then doing your logic in an IF statement ?

At the very least put the logic in a WHERE clause:
``````   FOR Calendar_Dates IN (
SELECT holiday FROM temp_calendar
WHERE holiday = v_pr_day
)
LOOP
v_pr_day := fn_test (v_pr_day);
END LOOP;``````
Or rewrite that loop as a SELECT INTO and an exception section, since with the where clause you would only be selecting one or zero rows.

Anyway, the SQL solution will be good and fast - I am just pointing out a bit of bad, bad code, so you may learn how to do it better when you write another function/procedure ;-)
1 person found this helpful
• ###### 5. Re: Holiday calendar table logic required in sql instead of pl sql
user8941550 wrote:
Hi Jeenesh,

Thanks for the answer.
But do you think that recursion is not the right thing to do here in terms of performance or in terms of good coding.

Thanks..
And to add to all the comments above -

Another method is to have a Holiday calendar with all the dates and a holiday flag column..

Again, you will have to decide based on your requirements.. If you are frequently using holidays and business days in your reports/programmes, I will suggest to have a table as mentioned above..for 100 years, the table will have ONLY 36,500 entris, which is nothing in any ORACLE database..If required, add an index to it..
1 person found this helpful
• ###### 6. Re: Holiday calendar table logic required in sql instead of pl sql
If you really want pure SQL Solution, foolproof, without recursion, this will work
``````SELECT MIN (last_working_day)
FROM   (SELECT period_start - 1 last_working_day
FROM   (SELECT   MIN (holiday) period_start, MAX (holiday) period_end
FROM     (SELECT holiday, MAX (contig) OVER (ORDER BY holiday) contiguous_group
FROM   (SELECT holiday,
LAG (holiday) OVER (ORDER BY holiday),
ROW_NUMBER () OVER (ORDER BY holiday),
CASE
WHEN LAG (holiday) OVER (ORDER BY holiday) != holiday - 1
OR              ROW_NUMBER () OVER (ORDER BY holiday) = 1 THEN ROW_NUMBER () OVER (ORDER BY holiday)
ELSE NULL
END
contig
FROM   temp_calendar))
GROUP BY contiguous_group) holiday_range
WHERE  :input_date - 1 BETWEEN period_start AND period_end
UNION ALL
SELECT :input_date - 1 last_working_day FROM DUAL)``````
This will overcome the limitation of 30 days posted by Jeneesh
1 person found this helpful
• ###### 7. Re: Holiday calendar table logic required in sql instead of pl sql
Hi,

this might do it :
``````create or replace FUNCTION fn_test (in_date IN DATE)
return date
is
l_ret date;
begin
select startdt-1
into l_ret
from (
select min(holiday) startdt, max(holiday) enddt
from (
select id, holiday, holiday-(row_number() over (order by holiday)) grp
from temp_calendar
)
group by grp
) v
where in_date-1 between startdt and enddt;
return l_ret;
exception when no_data_found then
return in_date-1;
end fn_test;
/``````
Test :
``````[11.2] Scott @ My11g > l
1  declare
2  l_date1 date := to_date('8-JAN-2012','dd-Mon-yyyy');
3  l_date2 date := to_date('7-JAN-2012','dd-Mon-yyyy');
4  begin
5  null;
6  dbms_output.put_line(l_date1 ||' => '||fn_test(l_date1));
7  dbms_output.put_line(l_date2 ||' => '||fn_test(l_date2));
8* end;
[11.2] Scott @ My11g > /
08/01/2012 00:00:00 => 07/01/2012 00:00:00
07/01/2012 00:00:00 => 04/01/2012 00:00:00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02``````
• ###### 8. Re: Holiday calendar table logic required in sql instead of pl sql
Thanks everyone for the answers..:-)
I now have so many ways to implement this.
• ###### 9. Re: Holiday calendar table logic required in sql instead of pl sql
Perfect Nicosa. You simplified my issue of creating holiday range. :)
• ###### 10. Re: Holiday calendar table logic required in sql instead of pl sql
>
I now have so many ways to implement this.
>
Ah, you can always use even MORE ways to do things don't you think?

You didn't say
1. how many times wll the function get called by the same session?
2. will the function get called by multiple sessions?
3. how many 'holidays' do you need to deal with?

Solution #1

You can put your function in a package and in the package spec define an associative array to hold the holidays.

Then in the package INITIALIZATION section you add code to query the holiday and load the associative array.

Then you modify your function to check to see if the date-1 you are using is in the associative array.

That way the holiday table is only read ONE time no matter how many times the session calls the function.

Solution #2

Create a GLOBAL CONTEXT and when your application starts (or instance) load the global context with the name/value pairs by reading the holiday table.

Then modify your function to query the global context to see if the date is a holiday or not. You don't need recursion, you can just use a loop that keeps looping until a non-holiday is found.

Again the holiday table is only read ONE time no matter how many sessions call the function or how many times they call it. The global context acts like a global in-memory table so the actual holiday table is never read again after the application starts. Very fast and is also efficient if the number of holidays (size of the in-memory table) is relatively small as it should be. Even 20 holidays a year for 10 years is just 200 entries.