This content has been marked as final. Show 10 replies
There are several methods available
Assuming you wont have 30 consecutive holidays
Or you can use recursion..
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);
Edited by: jeneesh on Nov 22, 2012 1:06 PM
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:Why oh why are you looping over all the holidays and then doing your logic in an IF statement ?
FOR Calendar_Dates IN (SELECT holiday FROM temp_calendar)
IF (v_pr_day = Calendar_Dates.holiday)
v_pr_day := fn_test (v_pr_day);
At the very least put the logic in a WHERE clause:
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.
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;
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 ;-)
user8941550 wrote:And to add to all the comments above -
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.
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..
If you really want pure SQL Solution, foolproof, without recursion, this will work
This will overcome the limitation of 30 days posted by Jeneesh
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 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; /
[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
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?
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.
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.