August 18, 2016 at 6:29 am
Hi everybody,
Starting from this table:
[font="Courier New"]POSEmployee_IDNameDateWkTimeStartWkTimeEnd
1896John01/07/201610.00.0013.00.00
1896John02/07/201610.00.0012.30.00
18178Dave11/07/20167.30.009.00.00
18122Paul01/07/201616.00.0020.00.00
[/font]
I'm triyng to write a query that can calculate working minutes for every date/hour slot
The resulting table should be something like this:
[font="Courier New"]POSEmployee_IDNameDateHourMinutes
1896John01/07/20161060
1896John01/07/20161160
1896John01/07/20161260
1896John02/07/20161060
1896John02/07/20161160
1896John02/07/20161230
18178Dave11/07/2016730
18178Dave11/07/2016860
18122Paul01/07/20161660
18122Paul01/07/20161760
18122Paul01/07/20161860
18122Paul01/07/20161960
[/font]
But actually i really don' t know where to start.
Thank you in advance!
August 18, 2016 at 7:21 am
-- sample data setup
DROP TABLE #MyTable
SELECT
POS,
Employee_ID,
[Name],
[Date] = CAST([Date] AS DATE),
WkTimeStart = CAST(WkTimeStart AS TIME),
WkTimeEnd = CAST(WkTimeEnd AS TIME)
INTO #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, 122, 'Paul', '01/07/2016', '16:00:00', '20:00:00'
) d (POS, Employee_ID, [Name], [Date], WkTimeStart, WkTimeEnd)
-- solution
;WITH iTally AS (
SELECT *
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)) d (n)
)
SELECT
m.POS, m.Employee_ID, m.[Name], m.[Date],
[Hour] = y.FirstHour+n,
[Minutes] = CASE
WHEN y.FirstHour + n = [LastHour] AND FirstHourShort = 0 THEN y.[Minutes]
WHEN n = 0 AND FirstHourShort = 1 THEN y.[Minutes]
ELSE 60 END
FROM #MyTable m
CROSS APPLY (
SELECT
[FirstHour] = DATEPART(HOUR,WkTimeStart),
[LastHour] = DATEPART(HOUR,WkTimeEnd),
[Minutes] = DATEDIFF(MINUTE,WkTimeStart, WkTimeEnd)%60,
FirstHourShort = CASE WHEN DATEPART(MINUTE,WkTimeStart) > 0 THEN 1 ELSE 0 END
) y
CROSS APPLY (
SELECT TOP(
DATEDIFF(HOUR,WkTimeStart, WkTimeEnd)
+ CASE WHEN y.[Minutes] > 0 THEN 1 ELSE 0 END
- FirstHourShort
)
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM iTally
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2016 at 9:25 am
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply