Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    56

    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..

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    251
    Code:
    dayneo@RMSD> create or replace function fn_test(in_date in date) return date is
      2    v_pr_day date;
      3  begin
      4
      5    select holiday
      6      into v_pr_day
      7      from (select holiday
      8              from temp_calendar
      9            connect by prior holiday-1 = holiday
     10            start with holiday = trunc(in_date)-1
     11             order by holiday asc)
     12    where rownum <= 1;
     13
     14    return v_pr_day-1;
     15
     16  exception
     17    when NO_DATA_FOUND then
     18      return trunc(in_date)-1;
     19
     20  END fn_test;
     21  /
    
    Function created.
    
    dayneo@RMSD> Select fn_test('8-JAN-2012') from dual;
    
    FN_TEST('
    ---------
    07-JAN-12
    
    dayneo@RMSD> Select fn_test('7-JAN-2012') from dual;
    
    FN_TEST('
    ---------
    04-JAN-12
    
    dayneo@RMSD>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •