This discussion is archived
10 Replies Latest reply: Nov 22, 2012 8:53 PM by rp0428 RSS

Holiday calendar table logic required in sql instead of pl sql

user8941550 Newbie
Currently Being Moderated
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
    jeneesh Guru
    Currently Being Moderated
    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
  • 2. Re: Holiday calendar table logic required in sql instead of pl sql
    user8941550 Newbie
    Currently Being Moderated
    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
    AlbertoFaenza Expert
    Currently Being Moderated
    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
  • 4. Re: Holiday calendar table logic required in sql instead of pl sql
    Kim Berg Hansen Expert
    Currently Being Moderated
    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 ;-)
  • 5. Re: Holiday calendar table logic required in sql instead of pl sql
    jeneesh Guru
    Currently Being Moderated
    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..
  • 6. Re: Holiday calendar table logic required in sql instead of pl sql
    sukhijank Explorer
    Currently Being Moderated
    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
  • 7. Re: Holiday calendar table logic required in sql instead of pl sql
    Nicosa Expert
    Currently Being Moderated
    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
    user8941550 Newbie
    Currently Being Moderated
    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
    sukhijank Explorer
    Currently Being Moderated
    Perfect Nicosa. You simplified my issue of creating holiday range. :)
  • 10. Re: Holiday calendar table logic required in sql instead of pl sql
    rp0428 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points