sqloracle-database

adding business days in oracle sql


I have two date fields, DATE_FIELD_ONE = 8/30/2018 and DATE_FIELD_TWO = DATE_FIELD_ONE + 20. I need to find what DATE_FIELD_TWO should be if I'm only added 20 business days . How would I accomplish this? I thought maybe trying 'DY' but not sure how to get it to work. Thanks.

CASE WHEN TO_CHAR(TO_DATE(DATE_FIELD_ONE),'DY')='SAT' THEN 1 ELSE 0 END
CASE WHEN TO_CHAR(TO_DATE(DATE_FIELD_ONE),'DY')='SUN' THEN 1 ELSE 0 END

Solution

  • You may try this :

    select max(date_field_two) as date_field_two
      from
     (
     select date'2018-08-30'+  
        cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH') 
                                                  in ('6','7') then 
                0
              else
                level
              end as int) as date_field_two, 
     sum(cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')  
                                                   in ('6','7') then 
                0
              else
                1
              end as int)) over (order by level) as next_day
          from dual
        connect by level <= 20*1.5 
    -- 20 is the day to be added, every time 5(#of business days)*1.5 > 7(#of week days)
    -- 7=5+2<5+(5/2)=5*(1+1/2)=5*1.5 [where 1.5 is just a coefficient might be replaced a greater one like 2]
    -- so 4*5*1.5=20*1.5 > 4*7 
      )    
     where next_day = 20;
    
     DATE_FIELD_TWO
    -----------------
       27.09.2018
    

    by using connect by dual clause.

    P.S. Ignored the case for public holidays, which differ from one culture to another , depending on the question being related with only weekends.

    Rextester Demo

    Edit : Assume you have a national holidays on '2018-09-25' and '2018-09-26' (in this set of days), then consider the following :

    select max(date_field_two) as date_field_two
      from
     (
     select date'2018-08-30'+  
            (case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH') 
                                                  in ('6','7') then 
                   0
                  when date'2018-08-30'+level in (date'2018-09-25',date'2018-09-26') then
                   0
                  else
                   level
                  end) as date_field_two, 
     sum(cast(case when to_char(date'2018-08-30'+level,'D','NLS_DATE_LANGUAGE=ENGLISH')  
                                                   in ('6','7') then 
                    0
                   when date'2018-08-30'+level in (date'2018-09-25',date'2018-09-26') then
                    0 
                   else
                    1
                   end as int)) over (order by level) as next_day
          from dual
        connect by level <= 20*2 
      )    
     where next_day = 20;
    
     DATE_FIELD_TWO
    -----------------
       01.10.2018
    

    which iterates one day next, as in this case, unless this holiday coincides with weekend.