Find missing records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find missing records

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-04 : 11:34:58

Hi,

I have a table with records and I wish to know if I have data for a range of hour(08:00 to 23:00).
Id Office Hour Data
1 A 08:00 4
2 A 09:00 7
3 A 11:00 12
.
.
18 B 08:00 5
19 B 09:00 2...etc

If data is missing for Office A at 10:00 Then I wish to insert a record with the Office = A , Hour = 10:00 and Data = -1

Any idea?
Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 15:26:44

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;

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-04 : 16:18:46

Give the man a cigar!
You are a genius sunitabeck.
Thank you so much!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-04 : 16:19:33

give the woman a cigar

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources