Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
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 Challenge 2

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!


Share

Comments

# re: TSQL Challenge 2

Thursday, February 27, 2014 1:55 AM by jamadar.ajim90

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

;WITH CTE_MAIN

AS

(

SELECT ID

,StartDate StartDate_original

,EndDate EndDate_original

,CASE WHEN DATEADD(HH,8,CONVERT(VARCHAR(10),StartDate,120))>StartDate THEN DATEADD(HH,8,CONVERT(VARCHAR(10),StartDate,120)) ELSE StartDate END StartDate

,

CASE WHEN DATEADD(HH,17,CONVERT(VARCHAR(10),(CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END),120))<

CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END

THEN DATEADD(HH,17,CONVERT(VARCHAR(10),(CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END),120))

ELSE CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END

END END_date

,DATEDIFF(MI

,(CASE WHEN DATEADD(HH,8,CONVERT(VARCHAR(10),StartDate,120))>StartDate THEN DATEADD(HH,8,CONVERT(VARCHAR(10),StartDate,120)) ELSE StartDate END)

,(

CASE WHEN DATEADD(HH,17,CONVERT(VARCHAR(10),(CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END),120))<

CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END

THEN DATEADD(HH,17,CONVERT(VARCHAR(10),(CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END),120))

ELSE CASE WHEN DATEPART(DD,StartDate)<DATEPART(DD,EndDate) THEN DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate,112)) ELSE EndDate END

END

)

)

min_

,DATEPART(DW,StartDate) part

FROM @t

UNION ALL

SELECT ID

,StartDate_original

,EndDate_original

,DATEADD(HH,08,CONVERT(VARCHAR(10),StartDate+1,120))

,CASE WHEN EndDate_original<DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate+1,120)) THEN EndDate_original ELSE DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate+1,120)) END EndDate

,DATEDIFF(MI,(DATEADD(HH,08,CONVERT(VARCHAR(10),StartDate+1,120))),(CASE WHEN EndDate_original<DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate+1,120)) THEN EndDate_original ELSE DATEADD(HH,17,CONVERT(VARCHAR(10),StartDate+1,120)) END)) min_

,DATEPART(DW,CONVERT(VARCHAR(10),DATEADD(DD,1,StartDate),120)) part

FROM CTE_MAIN

WHERE CONVERT(VARCHAR(10),DATEADD(DD,1,StartDate),120)<=CONVERT(VARCHAR(10),EndDate_original,120)

--AND DATEPART(DW,CONVERT(VARCHAR(10),DATEADD(DD,1,StartDate),120)) IN(2,3,4,5,6)

)

SELECT id

,StartDate_original StartDate

,EndDate_original EndDate

,SUM(HRS) Hours

,SUM(MIN) Minutes

FROM

(

SELECT id,StartDate_original,EndDate_original,StartDate,end_date,part

,CASE WHEN [MIN_]/60 >0 THEN [MIN_]/60 ELSE 0 END HRS

,CASE WHEN [MIN_]%60 >0 THEN [MIN_]%60 ELSE 0 END MIN

FROM CTE_MAIN

WHERE part BETWEEN 2 AND 6

) a

GROUP BY id,StartDate_original,EndDate_original

ORDER BY id,StartDate_original,EndDate_original


Copyright © Rivera Informatic Private Ltd.