Count Days between two dates and excluding Weekends

Question

How do I count days between two dates, excluding weekends?

I have this following data, I can simply getting the TAT Value by just doing ClosingDate - ActivityDate. However, I don't want to consider Saturday/Sunday.

+-------------------------+--------+-------------------------+------+
|      ActivityDate       | TypeID |       ClosedDate        | TAT  |
+-------------------------+--------+-------------------------+------+
| Wednesday, May 02, 2018 |   2502 | Wednesday, May 09, 2018 | 7.00 |
| Monday, May 07, 2018    |   2503 | Thursday, May 10, 2018  | 3.00 |
| Tuesday, May 08, 2018   |   2504 | Friday, May 11, 2018    | 3.00 |
| Wednesday, May 09, 2018 |   2505 | Thursday, May 10, 2018  | 1.00 |
| Thursday, May 10, 2018  |   2506 | Friday, May 11, 2018    | 1.00 |
| Friday, May 11, 2018    |   2507 | Thursday, May 17, 2018  | 6.00 |
| Thursday, May 10, 2018  |   2508 | Tuesday, May 15, 2018   | 5.00 |
| Monday, May 14, 2018    |   2509 | Wednesday, May 16, 2018 | 2.00 |
| Monday, May 14, 2018    |   2510 | Thursday, May 17, 2018  | 3.00 |
+-------------------------+--------+-------------------------+------+

Any help is much appreciated.

Possible duplicate of [Calculate business days in Oracle SQL(no functions or procedure)](https://stackoverflow.com/questions/14898357/calculate-business-days-in-oracle-sqlno-functions-or-procedure) — peter.hrasko.sk, Nov 12 '18 at 22:15

score 0 · Answer 1 · answered Nov 13 '18 at 05:55

How about something like this?

I took only a part of your data set. The idea is: check day number (to_char(date, 'd')); if day number of the activity date (AD) is larger than closed date (CD), then remove two additional days (weekends).

This might need to be adjusted if one (or both) of those dates fall into Saturday or Sunday (but, according to your sample data, they don't).

SQL> with test (ad, cd) as
  2    (select date '2018-05-02', date '2018-05-09' from dual union all
  3     select date '2018-05-11', date '2018-05-17' from dual union all
  4     select date '2018-05-10', date '2018-05-15' from dual
  5    ),
  6  day_num as
  7    (select ad, cd,
  8      to_char(ad, 'd') addn, to_char(cd, 'd') cddn,
  9      to_char(ad, 'dy') ady, to_char(cd, 'dy') cdy
 10     from test
 11    )
 12  select ad, cd,
 13    cd - ad - case when addn > cddn then 2 else 0 end diff
 14  from day_num;

AD              CD                    DIFF
--------------- --------------- ----------
02.05.2018, wed 09.05.2018, wed          7
11.05.2018, fri 17.05.2018, thu          4
10.05.2018, thu 15.05.2018, tue          3

SQL>

Count Days between two dates and excluding Weekends

1 Answers1