### Thread: Calculating working days between 2 dates

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Jul 2012
Posts
2
Rep Power
0

#### Calculating working days between 2 dates

Hi,

I found the following to return number of mon-fri days between 2 dates but need help to amend it so that it takes in to account public holidays from the Holiday table.

SELECT
top 100
application_id,
application_date as "Start date",
status_date as "End Date",
(DATEDIFF(dd, application_date, status_date) + 1) -(DATEDIFF(wk, application_date, status_date) * 2) -(
CASE WHEN DATENAME(dw, status_date) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, status_date) = 'Saturday' THEN 1 ELSE 0 END) as "Difference"
from applications

Holiday table (holiday_date):

2007-12-26 00:00:00.000
2008-01-01 00:00:00.000
2008-03-21 00:00:00.000
2008-03-24 00:00:00.000

Vijay
2. by far the easiest solution is to change your holidays table into a calendar table, which has one row per date

an additional column which indicates whether the date is a weekday will come in very handy

and of course a column for the date being a holiday
Code:
```thedate      wkday   holiday
2008-03-16     1        0
2008-03-17     2        1
2008-03-18     3        0
2008-03-19     4        0
2008-03-20     5        0
2008-03-21     6        1
2008-03-22     7        0
2008-03-23     1        0
2008-03-24     2        1
2008-03-25     3        0```
now your query is really easy --
Code:
```SELECT TOP 100
application_id
, application_date  AS "Start date"
, status_date       AS "End Date"
, COUNT(*) - 1      AS "Business Days"
FROM applications
INNER
JOIN calendar
ON calendar.thedate BETWEEN application_date AND status_date
AND calendar.wkday BETWEEN 2 and 5
AND calendar.holiday = 0
GROUP
BY application_id
, application_date
, status_date
ORDER
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Jul 2012
Posts
2
Rep Power
0
Thanks very much r937.

The only issue is that the connection is read-only and won't be able to create our own user tables.

Is the datediff / dateadd code usable? I appreciate it might not be the best solution!

Thanks again,

Vijay
4. Originally Posted by v1j4y
Is the datediff / dateadd code usable?
only if you can subtract out the holidays which occur between those dates

think about using COUNT(*) in a subquery, and subtracting that from your initial calculation

as for your read-only connection, what happens when the holiday table is out of date? whom do you tell?