This discussion is archived
7 Replies Latest reply: Apr 18, 2013 9:33 PM by Manik RSS

Find next n workday unique query

muttleychess Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    I hope you got the idea..

    You could test it and change it accordingly..
  • 5. Re: Find next n workday unique query
    muttleychess Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points