## Forum Stats

• 3,770,491 Users
• 2,253,125 Discussions

Discussions

# Reg: Count of non working/business days between 2 dates

Member Posts: 38 Green Ribbon

Hi Team,

We have 2 dates (Start_date and End_date), need to calculate count of non-business days (Sunday and Saturday) between these 2 dates. Can someone assist how to achieve this.

Tagged:

• Member Posts: 18,926 Red Diamond
edited May 3, 2021 4:36PM
```SELECT  COUNT(*)
FROM  DUAL
WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
/
```

For example weekdays in May 2021:

```DEFINE START_DATE="DATE '2021-05-01'"
DEFINE END_DATE="DATE '2021-05-31'"
SELECT  COUNT(*)
FROM  DUAL
WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
/
old   3:   WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
new   3:   WHERE (DATE '2021-05-01' + LEVEL - 1) - TRUNC(DATE '2021-05-01' + LEVEL - 1,'IW') < 5
old   4:   CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
new   4:   CONNECT BY DATE '2021-05-01' + LEVEL - 1 <= DATE '2021-05-31'

COUNT(*)
----------
21

SQL>

```

SY.

• Member, Moderator Posts: 41,233 Red Diamond

Hi, @User_JZKI6

See

and/or

This approach can handle holidays, too.

• Member Posts: 1,114 Gold Trophy

You can also try this:

select least(7-to_char(:p_start_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+1,2)

+greatest(to_char(:p_end_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')-5,0)

+((trunc(:p_end_date,'IW')-trunc(:p_start_date,'IW'))/7-1)*2 as cnt

from dual

• Member Posts: 38 Green Ribbon
edited May 4, 2021 11:16AM

Thank you for all your replies, this worked for me, our main task is to find the no. of business day’s between 2 dates (Start_date and End_date) that should exclude weekends (non-working days) and public holidays.

Initially we took the actual diff between 2 dates, for the results we excluded the count of weekends, now how we can find the count of public holidays if we exclude with the result then we will get actual business days count.

We have time table having holiday_flag (‘Y’ or ‘N’) field how we can make of this and find the count of holidays any suggestions.

• Member Posts: 18,926 Red Diamond

We have time table having holiday_flag (‘Y’ or ‘N’) field how we can make of this and find the count of holidays any suggestions.

It would be simpler to have non-working day table with both weekends (say W) and holidays (say H). Anyway, I'll assume your time table has column DT:

```SELECT  COUNT(*)
FROM  DUAL
WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
AND ﻿&&START_DATE + LEVEL - 1 NOT IN (SELECT DT FROM TIME_TABLE WHERE HOLIDAY_FLAG = 'N')
CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
/
```

SY.

• Member Posts: 18,926 Red Diamond

Actually, it isn't clear what data you store in time table. If it does store all dates then:

```SELECT  COUNT(*)
FROM  TIME_TABLE
WHERE DT BETWEEN ﻿&&START_DATE AND &&END_DATE
AND DT - TRUNC(DT,'IW') < 5
AND HOLIDAY_FLAG = 'N'
/
```

SY.

• Member Posts: 38 Green Ribbon

Actually we have 2 tables Order_Start_date and Order_End_date are from Order table and holidayflag ('Y','N'') and weekend('Y','N') flag are from Time table.

Here we are joining the both tables as below

from order A ,time B

B. Time_Key between B. Order_Start_date and B. Order_End_date

Trying to find the no. business days using above 2 tables it should exclude the weekends(non-working days) and public holidays

• Member Posts: 18,926 Red Diamond

Still not clear. I'll assume table TIME has all dates (column DT) and two flag columns HOLIDAY and WEEKEND and you trying to find number of workdays for each order. If so:

```SELECT  O.*,
(
SELECT  COUNT(*)
FROM  TIME T
WHERE T.DT BETWEEN O.ORDER_START_DATE AND ORDER_END_DATE
AND T.HOLIDAY = 'N'
AND T.WEEKEND = 'N'
) ORDER_WORKDAY_COUNT
FROM  ORDER O
/
```

SY.