T-SQL Challenge #2

This puzzle is part of the TSQL Challenge contest.

Author: Jacob Sebastion

Introduction

The challenge is to calculate the Business Hours between StartDate and EndDate. Let us define Business Hours as the time between 8 AM and 5 PM, Monday to Friday.

If StartDate is Friday 12 Noon and EndDate is Monday 10 AM, you should count only the duration between 12 Noon and 5 PM on friday and 8AM to 10 AM on monday.

Sample Data

Here is the sample data for TSQL Challenge 2

1
2
3
4
5
6
7
8
ID          StartDate               EndDate
----------- ----------------------- -----------------------
1           2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2           2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3           2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4           2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5           2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6           2009-01-27 21:15:00.000 2009-01-28 09:15:00.000

The task is to write a query that produces the following output from the above tables.

Expected Results

1
2
3
4
5
6
7
8
StartDate               EndDate                 Hours       Minutes
----------------------- ----------------------- ----------- -----------
2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 7           0
2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 15          0
2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 9           0
2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 7           45
2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0           0
2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 1           15

Rules

Sample Script

1
2
3
4
5
6
7
8
9
10
11
SET DATEFORMAT MDY
 
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15
 
SELECT * FROM @t

Restrictions

Notes