10 Replies Latest reply: Nov 22, 2012 10:53 PM by rp0428 RSS

    Holiday calendar table logic required in sql instead of pl sql

    user8941550
      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
          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
            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
              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
                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
                  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
                    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-Oracle
                      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
                        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
                          Perfect Nicosa. You simplified my issue of creating holiday range. :)
                          • 10. Re: Holiday calendar table logic required in sql instead of pl sql
                            rp0428
                            >
                            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.