Calculate Business Days in Oracle SQL(No Functions or Procedure)

Calculate business days in Oracle SQL(no functions or procedure)

The solution, finally:

SELECT OrderNumber, InstallDate, CompleteDate,
  (TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
  ((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
  (CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
  (CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;

Thanks for all your responses !

adding business days in oracle sql

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.

oracle sql: efficient way to calculate business days in a month

This approach uses sub-query factoring - what other RDBMS flavours call common table expressions. The attraction here is that we can pass the output from one CTE as input to another. Find out more.

The first CTE generates a list of dates in a given month (you can extend this over any range you like).

The second CTE uses an anti-join on the first to filter out dates which are holidays and also dates which aren't weekdays. Note that Day Number varies depending according to the NLS_TERRITORY setting; in my realm the weekend is days 6 and 7 but SQL Fiddle is American so there it is 1 and 7.

with dates as ( select date '2014-04-01' + ( level -  1) as d
               from dual
               connect by level <= 30 )
     , bdays as ( select d
                         , count(d) over () tot_d 
                  from   dates
                         left join holidays
                              on dates.d = holidays.hol_date
                  where holidays.hol_date is null   
                  and   to_number(to_char(dates.d, 'D')) between 2 and 6 
                )
select yt.account
       , yt.txn_date
       , sum(yt.amount) over (partition by yt.account, trunc(yt.txn_date,'MM'))
            /tot_d as avg_amt
from your_table yt
     join bdays 
          on bdays.d = yt.txn_date
order by yt.account
       , yt.txn_date
/

I haven't rounded the average amount.

---

Related Topics