TSQL Challenge 2

Getting Started with Adobe After Effects - Part 6: Motion Blur

First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenges


Subscribe By Email

Pushing database changes needn't be hard work with SQL Compare

Previous Challenges

Top Winners

	leszek_g	232
	Stefan_G	176
	dishdy	106
	malpashaa	101
	Mark	86
View all

Early Review Team

TSQL Challenges Team

	Jacob Sebastian
	Rui Carvalho
	Brad Schulz
	Vijaya Krishna Kadiyala
	Barbarin David
	Peter Larsson
	Mangal Pardeshi
	Alejandro Messa
	Adam Haines
	Rob Farley

Submit A Solution

I would like to thank all the participants of TSQL Challenge 1 and congratulate the winners. Let us move ahead to the next challenge.

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

The query should return the following result.

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

Here is the script to generate the sample data.

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

Note: Please write your query using the sample data provided. This will make it easier for me to test your code.

Happy Querying!