Calculating working days between 2 dates

Dev Shed Forums Sponsor:

July 18th, 2012, 10:59 AM

v1j4y

Registered User

Join Date: Jul 2012

Posts: 2

Time spent in forums: 15 m 40 sec
Reputation 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

Thanks in advance

Vijay

July 18th, 2012, 12:20 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 28 m 13 sec
Reputation Power: 4140

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
    BY "Business Days" DESC

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 18th, 2012, 03:18 PM

v1j4y

Registered User

Join Date: Jul 2012

Posts: 2

Time spent in forums: 15 m 40 sec
Reputation 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

July 18th, 2012, 05:58 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 28 m 13 sec
Reputation Power: 4140

Quote:

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?

Calculating working days between 2 dates

Developer Shed Advertisers and Affiliates