Quick alternative to Chris's solution, this one catches better the fractional end hours
😎
USE TEEST;
GO
SET NOCOUNT ON;
--http://www.sqlservercentral.com/Forums/Topic1810466-3077-1.aspx
--/* -- UNCOMMENT THIS LINE TO SKIP THE SETUP
-- sample data setup
IF OBJECT_ID(N'dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable
SELECT
POS,
Employee_ID,
[Name],
[Date] = CAST([Date] AS DATE),
WkTimeStart = CAST(WkTimeStart AS TIME),
WkTimeEnd = CAST(WkTimeEnd AS TIME)
INTO dbo.MyTable
FROM (
SELECT 18, 96, 'John', '01/07/2016', '10:00:00', '13:00:00' UNION ALL
SELECT 18, 96, 'John', '02/07/2016', '10:00:00', '12:30:00' UNION ALL
SELECT 18, 178, 'Dave', '11/07/2016', '7:30:00', '9:00:00' UNION ALL
SELECT 18, 179, 'Peter', '11/07/2016', '7:30:00', '9:05:00' UNION ALL
SELECT 18, 180, 'SAM', '11/07/2016', '7:00:00', '8:00:00' UNION ALL
SELECT 18, 180, 'SAM', '12/07/2016', '7:01:00', '8:00:00' UNION ALL
SELECT 18, 122, 'Paul', '01/07/2016', '16:00:00', '20:00:00'
) d (POS, Employee_ID, [Name], [Date], WkTimeStart, WkTimeEnd)
--*/
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0)) AS X(N))
SELECT
MT.POS
,MT.Employee_ID
,MT.Name
,MT.Date
,FIRST_HOUR + N AS [Hour]
,CASE
WHEN N = 0 THEN Y
WHEN N = (NUM_HOURS - 1) AND LAST_HOUR > LAST_HOUR_MIN THEN Z
WHEN N + FIRST_HOUR BETWEEN FIRST_HOUR_MAX AND LAST_HOUR_MIN THEN 60
ELSE 42
END AS [Minutes]
FROM dbo.MyTable MT
CROSS APPLY
(
SELECT
DATEDIFF(SECOND,CONVERT(TIME,'00:00:00',0),MT.WkTimeStart) AS START_SEC_FROM_MIDNIGHT
,DATEDIFF(SECOND,CONVERT(TIME,'00:00:00',0),MT.WkTimeEnd) AS END_SEC_FROM_MIDNIGHT
) AS BASE_CALC
CROSS APPLY
(
SELECT
FLOOR(START_SEC_FROM_MIDNIGHT / 3600.0) AS FIRST_HOUR
,CEILING(START_SEC_FROM_MIDNIGHT / 3600.0) AS FIRST_HOUR_MAX
,CEILING(END_SEC_FROM_MIDNIGHT / 3600.0) AS LAST_HOUR
,FLOOR(END_SEC_FROM_MIDNIGHT / 3600.0) AS LAST_HOUR_MIN
) AS LASTHR
CROSS APPLY
(
SELECT
CONVERT(INT,LAST_HOUR - FIRST_HOUR,0) AS NUM_HOURS
,FIRST_HOUR_MAX - FIRST_HOUR AS X
,60 - ((START_SEC_FROM_MIDNIGHT % 3600) / 60) AS Y
,((END_SEC_FROM_MIDNIGHT % 3600) / 60) AS Z
) AS HOURS_CALC
CROSS APPLY
(
SELECT
TOP(NUM_HOURS) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM T
) AS NM
;
Output
POS Employee_ID Name Date Hour Minutes
----- ----------- ----- ---------- ------ -----------
18 96 John 2016-01-07 10 60
18 96 John 2016-01-07 11 60
18 96 John 2016-01-07 12 60
18 96 John 2016-02-07 10 60
18 96 John 2016-02-07 11 60
18 96 John 2016-02-07 12 30
18 178 Dave 2016-11-07 7 30
18 178 Dave 2016-11-07 8 60
18 179 Peter 2016-11-07 7 30
18 179 Peter 2016-11-07 8 60
18 179 Peter 2016-11-07 9 5
18 180 SAM 2016-11-07 7 60
18 180 SAM 2016-12-07 7 59
18 122 Paul 2016-01-07 16 60
18 122 Paul 2016-01-07 17 60
18 122 Paul 2016-01-07 18 60
18 122 Paul 2016-01-07 19 60