Counting working days between two dates Oracle

Question

I'm trying to write a query which can count the number of working days between a payment being received and being processed, I started playing around with this for payments received in December 2017;

select unique trunc(date_received), 
   (case when trunc(date_received) in ('25-DEC-17','26-DEC-17') Then 0 when 
to_char(date_received,'D') <6 Then 1 else 0 end) Working_day
from payments
where date_received between '01-DEC-17' and '31-dec-17'
order by trunc(date_received)

But to be honest, I'm at a loss as to how to take it further and add in date_processed and count the number of working days between date_processed and date_received... Any help would be much appreciated...

You can refer this link [Count Work Days](https://stackoverflow.com/questions/252519/count-work-days-between-two-dates) — Sushant Sharma, Dec 17 '18 at 11:59
I'd recommend a calendar table, with specified working and non-working days. — jarlh, Dec 17 '18 at 12:03
And in that calendar table add a column `workday_number` with a running number (using a Cumulative Sum over 1/0) which is increased only for working days. Your calculatiuon is then based on two joins (`date_received` & `date_processed`) to this calendar and a simple `t1.workday_number - t2.workday_number` — dnoeth, Dec 17 '18 at 12:25
Maybe check this https://stackoverflow.com/questions/41936398/calculate-hours-based-on-business-hours-in-oracle-sql/41937356#41937356 or https://stackoverflow.com/questions/29652394/sql-difference-between-two-dates/29658332#29658332 — Wernfried Domscheit, Dec 17 '18 at 12:26

score 2 · Answer 1 · answered Dec 17 '18 at 13:46

Maybe not the most optimal, but it works quite nicely, and it's easy to incorporate more complicated checks, like holidays. This query first generates all dates between the two dates, and then lets you filter out all the days that 'don't count'.

In this implementation I filtered out only weekend days, but it's quite easy to add checks for holidays and such.

with 
  -- YourQuery: I used a stub, but you can use your actual query here, which 
  -- returns a from date and to date. If you have multiple rows, you can also
  -- output some id here, which can be used for grouping in the last step.
  YourQuery as
  ( 
    select 
      trunc(sysdate - 7) as FromDate,
      trunc(sysdate) as ToDate
    from dual),

  -- DaysBetween. This returns all the dates from the start date up to and
  -- including the end date.
  DaysBetween as
  (  
    select
      FromDate,
      FromDate + level - 1 as DayBetween,
      ToDate
    from
      YourQuery
    connect by
      FromDate + level - 1 <= ToDate)

-- As a last step, you can filter out all the days you want. 
-- This default query only filters out Saturdays and Sundays, but you
-- could add a 'not exists' check that checks against a table with known 
-- holidays.
select
  count(*)
from
  DaysBetween d
where
  trim(to_char(DAYINBETWEEN, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN'))
    not in ('SATURDAY', 'SUNDAY');

Counting working days between two dates Oracle

1 Answers1