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 |