This content has been marked as final.
Show 7 replies

1. Re: Find next n workday unique query
jeneesh Apr 18, 2013 9:54 AM (in response to muttleychess)Something like..
Edited by: jeneesh on Apr 18, 2013 7:17 PMSQL> with t as 2 ( 3 select to_date('18042013','ddmmyyyy') dt, 4 8 no_of_days 5 from dual 6 ), t2 as 7 ( 8 select dt, 9 case to_char(dt,'fmday') 10 when 'monday' then 4 11 when 'tuesday' then 3 12 when 'wednesday' then 2 13 when 'thursday' then 1 14 when 'friday' then 0 15 when 'saturday' then 1 16 when 'sunday' then 2 17 end dif,no_of_days 18 from t 19 ) 20 select dt,no_of_days, 21 case when no_of_days <= dif then dt+no_of_days 22 else dt+dif+(no_of_daysgreatest(dif,0))+ 23 (ceil((no_of_daysgreatest(dif,0))/5)*2) 24 end next_dt 25 from t2; DT NO_OF_DAYS NEXT_DT    18APR13 8 30APR13
Not thoroughly tested..It will give you a starting point, if you dont want to do row generation.. 
2. Re: Find next n workday unique query
Frank Kulash Apr 18, 2013 8:53 AM (in response to muttleychess)Hi,
Here's one way:
How this works is that the subquery all_days generates a result set containing the upcoming work days, and the main query merely picks the right one.VARIABLE n_days NUMBER EXEC :n_days := 5; WITH all_days AS ( SELECT SYSDATE + LEVEL AS a_date , ROWNUM AS rnum FROM dual WHERE TO_CHAR ( SYSDATE + LEVEL , 'DY' , 'NLS_DATE_LANGUAGE=ENGLISH' ) NOT IN ('SAT', 'SUN') CONNECT BY LEVEL <= 2 + (:n_days * 7 / 5) ) SELECT a_date FROM all_days WHERE rnum = :n_days ;
The tricky part is making sure that the subquery gerenates enough days. Since there are 5 work days in every 7 consecutive days, then (normally) the nth work day will come in the next n * 7/5 days. However, if you run this on a weekend, or right before a weekend, then the 2 days of that weekend may interfere wil that calculation, so I added 2 days to guarantee that all_days has enough days. 
3. Re: Find next n workday unique query
muttleychess Apr 18, 2013 9:52 AM (in response to jeneesh)jeneesh wrote:
Thank you very much , What bug without
Something like..
Edited by: jeneesh on Apr 18, 2013 7:17 PMSQL> with t as 2 ( 3 select to_date('18042013','ddmmyyyy') dt, 4 8 no_of_days 5 from dual 6 ), t2 as 7 ( 8 select dt, 9 case to_char(dt,'fmday') 10 when 'monday' then 4 11 when 'tuesday' then 3 12 when 'wednesday' then 2 13 when 'thursday' then 1 14 when 'friday' then 0 15 when 'saturday' then 1 16 when 'sunday' then 2 17 end dif,no_of_days 18 from t 19 ) 20 select dt,no_of_days, 21 case when no_of_days <= dif then dt+no_of_days 22 else dt+dif+(no_of_daysgreatest(dif,0))+ 23 (ceil((no_of_daysdif)/5)*2) 24 end next_dt 25 from t2; DT NO_OF_DAYS NEXT_DT    18APR13 8 30APR13
Not thoroughly tested..It will give you a starting point, if you dont want to do row generation..
when 'saturday' then 1 when 'sunday' then 2

4. Re: Find next n workday unique query
jeneesh Apr 18, 2013 9:56 AM (in response to muttleychess)I hope you got the idea..
You could test it and change it accordingly.. 
5. Re: Find next n workday unique query
muttleychess Apr 18, 2013 10:01 AM (in response to jeneesh)jeneesh wrote:
Thank you
I hope you got the idea..
You could test it and change it accordingly..
You are a genius 
6. Re: Find next n workday unique query
Manik Apr 18, 2013 11:33 PM (in response to jeneesh)I know this is already answered...
May be even this works  modify that digit 5 based on your requirement.. (Tried to make it look simple)
(Did not check all the test cases though)
Cheers,WITH t AS ( SELECT COUNT ( CASE WHEN TO_CHAR (SYSDATE + ROWNUM, 'fmDY') IN ('SAT', 'SUN') THEN 1 END) cnt FROM DUAL CONNECT BY ROWNUM <= 5) SELECT SYSDATE + 5 + cnt + MOD (cnt, 2) FROM t;
Manik. 
7. Re: Find next n workday unique query
Ramin Hashimzadeh Apr 18, 2013 11:55 PM (in response to muttleychess)very very years ago i wrote some thing like this )))create or replace function GL_CDT(BSNS_DATE in date, ddays in number, dtype in varchar2) return date is BSNS_DATE date ddays number of days dtype is getting 2 symbolbs (W working day, C calendar day) Result date; begin if dtype = 'C' then result := BSNS_DATE+ddays; end if; if dtype = 'W' then select case when ddays > 0 then min(gl_WDAY) when ddays < 0 then max(gl_WDAY) else BSNS_DATE end into result from ( select wday as gl_WDAY, (case when ddays > 0 then rank() over (order by wday ) when ddays < 0 then rank() over (order by wday desc) else 1 end) as srt from (select (case when ddays > 0 then trunc(BSNS_DATE) + level when ddays < 0 then trunc(BSNS_DATE)  level else trunc(BSNS_DATE) end) as WDAY from dual connect by level <= ddays*7/5) where to_char(wday, 'DY') not in ('SAT', 'SUN') AND wday NOT IN (select TO_DATE(t.app_VALUE1, 'DD.MM.YYYY') from rr_app_resource.app_VARIABLE t WHERE T.app_VRB = 'HOLIDAY') order by srt desc ) where srt = abs(ddays); end if; return(Result); END;