7 Replies Latest reply: Apr 18, 2013 11:33 PM by Manik RSS

    Find next n workday unique query

    muttleychess
      Hi

      I need return unique query the next workday after n days from a date.
      workday is all days (too holidays) with exception satudarday and sunday


      I found some query count workdays betweeen two dates, but I can not get
      to build a unique query (without to use function) to add n workdays eg:

      Today is 18/04/2013 I want to add 5 workdays then

      the next work day is 25/04/2013

      if add 8 workdays the will be 30/04/2013

      How can do it ?

      Using 9.2.08
        • 1. Re: Find next n workday unique query
          jeneesh
          Something like..
          SQL> 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_days-greatest(dif,0))+
           23             (ceil((no_of_days-greatest(dif,0))/5)*2)
           24     end next_dt
           25  from t2;
          
          DT        NO_OF_DAYS NEXT_DT
          --------- ---------- ---------
          18-APR-13          8 30-APR-13
          Edited by: jeneesh on Apr 18, 2013 7:17 PM
          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
            Hi,

            Here's one way:
            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
            ;
            How this works is that the sub-query all_days generates a result set containing the upcoming work days, and the main query merely picks the right one.
            The tricky part is making sure that the sub-query gerenates enough days. Since there are 5 work days in every 7 consecutive days, then (normally) the n-th 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
              jeneesh wrote:
              Something like..
              SQL> 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_days-greatest(dif,0))+
              23             (ceil((no_of_days-dif)/5)*2)
              24     end next_dt
              25  from t2;
              
              DT        NO_OF_DAYS NEXT_DT
              --------- ---------- ---------
              18-APR-13          8 30-APR-13
              Edited by: jeneesh on Apr 18, 2013 7:17 PM
              Not thoroughly tested..It will give you a starting point, if you dont want to do row generation..
              Thank you very much , What bug without
                     when 'saturday' then -1
                     when 'sunday' then -2
              • 4. Re: Find next n workday unique query
                jeneesh
                I hope you got the idea..

                You could test it and change it accordingly..
                • 5. Re: Find next n workday unique query
                  muttleychess
                  jeneesh wrote:
                  I hope you got the idea..

                  You could test it and change it accordingly..
                  Thank you

                  You are a genius
                  • 6. Re: Find next n workday unique query
                    Manik
                    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)
                    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;
                    Cheers,
                    Manik.
                    • 7. Re: Find next n workday unique query
                      Ramin Hashimzadeh
                      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;