May 9, 2011 at 3:37 pm
I see lots of examples where one needs to parse a csv list to find the gaps of missing values or a single column integer and its missing range of values. I need to find the gaps of missing numbers in two 24 hour time-stamp (Hour) columns. Two columns, one row, like:
DECLARE @TestTVTABLE
(
DepartmentNVARCHAR(100),
ReceiptNVARCHAR(100),
StartDateDATETIME,
EndDateDATETIME,
HourStartINT,
HourCompleteINT
)
INSERT INTO @TestTV VALUES('GM','BFI110429083349','04/29/2011','04/29/2011',6,8)
INSERT INTO @TestTV VALUES('Grocery','DWH110429032648','04/28/2011','04/29/2011',23,3)
And then transform the data into another table variable like this:
DECLARE @TestTVTABLE
(
DepartmentNVARCHAR(100),
ReceiptNVARCHAR(100),
StartDateDATETIME,
EndDateDATETIME,
HoursActiveINT
)
INSERT INTO @TestTV VALUES('GM','BFI110429083349','04/29/2011','04/29/2011',6)
INSERT INTO @TestTV VALUES('GM','BFI110429083349','04/29/2011','04/29/2011',7)
INSERT INTO @TestTV VALUES('GM','BFI110429083349','04/29/2011','04/29/2011',8)
INSERT INTO @TestTV VALUES('Grocery','DWH110429032648','04/28/2011','04/29/2011',23)
INSERT INTO @TestTV VALUES('Grocery','DWH110429032648','04/28/2011','04/29/2011',0)
INSERT INTO @TestTV VALUES('Grocery','DWH110429032648','04/28/2011','04/29/2011',1)
INSERT INTO @TestTV VALUES('Grocery','DWH110429032648','04/28/2011','04/29/2011',2)
INSERT INTO @TestTV VALUES('Grocery','DWH110429032648','04/28/2011','04/29/2011',3)
SELECT * FROM @TestTV
Thanks.
May 9, 2011 at 4:26 pm
Like so?
;
WITH cte AS
(
SELECT Department,
Receipt,
StartDate,
EndDate,
HourStart,
HourComplete,
-- combine the hours into the appropriate date column, and get the difference in hours
-- this way, if someone is on the clock > 1 day, it will still work!
HourDiff = DATEDIFF(HOUR, DATEADD(HOUR, HourStart, StartDate), DATEADD(HOUR, HourComplete, EndDate))
FROM @TestTV t1
)
SELECT t1.Department,
t1.Receipt,
t1.StartDate,
t1.EndDate,
-- add each incremental hour to the starting hour, show the remainder when divided by 24 (modulus)
HoursActive = (t2.MyHour + t1.HourStart)%24
FROM cte t1
-- get each hour within the range
CROSS APPLY (SELECT MyHour = DATEPART(HOUR, DATEADD(HOUR, N-1, t1.StartDate))
FROM dbo.Tally
WHERE N BETWEEN 1 AND t1.HourDiff+1) t2;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 9, 2011 at 6:08 pm
A slight variation from Wayne's Code:
; WITH Tens (N) AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
)
, Hundreds (N) AS
(
SELECT T1.N FROM Tens T1 CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N FROM Hundreds T1 CROSS JOIN Hundreds T2
)
, NumbersTable(N) AS
(
SELECT 0
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM Thousands
),
DiffHours AS
(
SELECT Department , Receipt , StartDate , EndDate
,HourStart
,NumOfHours = DATEDIFF ( HH ,DATEADD ( HH , HourStart , StartDate) , DATEADD ( HH , HourComplete , EndDate) ) + 1
FROM @TestTV
)
SELECT DF.Department , DF.Receipt , DF.StartDate , DF.EndDate
, (( DF.HourStart + NT.N ) % 24 ) HoursActive
FROM DiffHours DF
CROSS JOIN NumbersTable NT
WHERE DF.NumOfHours > NT.N
May 10, 2011 at 1:30 am
Or...
SELECT
TTV.Department,
TTV.Receipt,
TTV.StartDate,
TTV.EndDate,
DV.HoursActive
FROM @TestTV AS TTV
JOIN
(
SELECT DISTINCT
V.HoursActive
FROM
(
VALUES
(00),(01),(02),(03),(04),(05),(06),(07),(08),(09),(10),(11),
(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
) AS V (HoursActive)
) AS DV ON
(TTV.HourStart < TTV.HourComplete AND DV.HoursActive >= TTV.HourStart AND DV.HoursActive <= TTV.HourComplete)
OR
(TTV.HourStart > TTV.HourComplete AND (DV.HoursActive <= TTV.HourComplete OR DV.HoursActive >= TTV.HourStart));
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 10, 2011 at 7:00 am
Thank you all very much. A few different techniques, great stuff. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply