This is perhaps easiest to do if you create a "calendar table" with the offices and the hours. Once you have that you can left join to that table and find the missing rows (and insert them into your table if you need to).
Here is how you can create a calendar table:CREATE TABLE #tmp (Office char(1),hr TIME PRIMARY KEY CLUSTERED (Office,hr));
;WITH cte1 AS
(
SELECT CAST('08:00' AS TIME) AS hr
UNION ALL
SELECT DATEADD(hour,1,hr) FROM cte1 WHERE hr < '23:00'
),
cte2 AS
(
SELECT DISTINCT office
FROM YourTable
)
INSERT INTO #tmp
SELECT a2.Office,a1.hr
FROM cte1 a1 CROSS JOIN cte2 a2;Now you can find the missing rows like this:SELECT
a.hr,
a.Office
FROM
#tmp a
LEFT JOIN YourTable b ON a.hr= b.[hour] AND a.Office = b.Office
WHERE
b.Office IS NULL;
Find missing records
Posted - 05/04/2012 : 11:34:58
