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
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
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
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