Determine Missing Hours

Question

Determine Missing Hours

Scott Solice

SSC Veteran

Points: 246
More actions
April 11, 2011 at 8:04 am

#234689

DECLARE @TestTVTABLE
(
[Type]NVARCHAR(1),
FacilityNVARCHAR(50),
DepartmentNVARCHAR(100),
LDateDATETIME,
CasesINT,
PalletsINT,
[Hour]INT,
ROWINT
)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',553,22,3,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GM','2011-03-25 00:00:00.000',4258,87,5,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',2623,125,5,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GM','2011-03-25 00:00:00.000',2342,55,6,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',286,7,6,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GM','2011-03-25 00:00:00.000',1857,105,7,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',4294,154,7,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GM','2011-03-25 00:00:00.000',2563,88,8,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GM','2011-03-25 00:00:00.000',390,22,9,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',5778,126,9,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GM','2011-03-25 00:00:00.000',553,46,11,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',484,33,12,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',4077,91,22,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GM','2011-03-25 00:00:00.000',491,18,23,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',7160,139,0,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',3439,144,1,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',19185,504,1,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',10042,194,3,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',22650,498,3,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',3218,107,4,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',708,20,4,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',1236,30,5,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',14223,367,5,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',15721,427,6,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',2356,33,6,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',3827,91,7,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',656,23,7,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',2971,80,8,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',6258,101,9,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',2361,58,10,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',1866,64,11,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',2370,31,12,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',0,25,12,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',4072,117,13,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',5240,40,14,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',1368,19,15,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',4985,89,16,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',5379,91,18,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',2735,40,19,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',2848,42,20,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',5828,108,20,2)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',1890,26,21,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',9510,179,22,1)
INSERT INTO @TestTV VALUES('F','Cheshire','GRO','2011-03-25 00:00:00.000',13587,276,23,1)
INSERT INTO @TestTV VALUES('S','Cheshire','GRO','2011-03-25 00:00:00.000',8065,185,23,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',1168,34,0,1)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',2290,67,1,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',7153,146,1,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',3310,50,2,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',430,18,2,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',2667,42,3,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',9272,155,3,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',11909,224,4,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',14904,184,5,1)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',2439,41,6,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',11028,212,6,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',8697,140,7,1)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',1316,46,8,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',3182,76,8,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',14937,125,9,1)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',6041,110,10,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',3552,52,10,2)
INSERT INTO @TestTV VALUES('F','Cheshire','PER','2011-03-25 00:00:00.000',1140,12,12,1)
INSERT INTO @TestTV VALUES('S','Cheshire','PER','2011-03-25 00:00:00.000',2110,291,12,2)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Scott Solice SSC Veteran Points: 246 More actions · Answer 1

I apologize for the post. It's very easy to accidently post the topic before you actually want to when editing.

Anyway, I'm looking for help in determining the missing hours in a 24 hour period by Facility, Department, and Date. I've review many solutions for finding the "missing number" and I understand the options there. The posted solutions all deal with a single number (column).

My problem is finding the missing number for the stated groups in 24 hour increments.

Thank you and I apologize again for the premature post.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

WITH Hours([Hour]) AS (

SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL

SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL

SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL

SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL

SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23),

TestTV AS (

SELECT DISTINCT Facility,Department,LDate

FROM @TestTV)

SELECT t.Facility,t.Department,t.LDate,h.[Hour]

FROM TestTV t

CROSS JOIN Hours h

EXCEPT

SELECT Facility,Department,LDate,[Hour]

FROM @TestTV

ORDER BY Facility,Department,LDate,[Hour];

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537