If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Holiday calendar table logic required in sql instead of pl sql

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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..
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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>
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On