Find the missing number - 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 2005 Forums

Transact-SQL (2005)

Find the missing number

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-07-08 : 13:31:17

I have a table that looks like this
200801
200802
200803
200805
200806
200807
200809

I would like to find the missing numbers i.e '200804' and '200808'

I appreciate any help....thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 13:40:59

[code]
DECLARE @Start datetime,@End datetime
SELECT @Start=MIN(DateCol),@End=MAX(DateCol)
FROM YourTable

;WIth Dates_CTE (DateVal)
AS
(
SELECT @Start
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Dates_CTE
WHERE DATEADD(dd,1,DateVal)<=@End
)

SELECT c.DateVal
FROM Dates_CTE c
LEFT JOIN YourTable t
ON t.DateCol=c.DateVal
WHERE t.DateCol IS NULL

OPTION (MAXRECURSION 0)
[/code]

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-07-08 : 13:43:48

Unfortunately I can't use CTE. I'm pulling the data from a 2000 server

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-08 : 13:47:01

create a number table and join it.

A number table can be created numerous ways, but here is a link to a few good methods.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

If you choose to use that function, your code would be similiar to as follows:



select * from
mytable a
left join
dbo.f_table_numbers (200800, 200810) b
on a.mycol = b.num
where b.num is null

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 13:47:28

here is a 2000 based soln


DECLARE @Start datetime,@End datetime
SELECT @Start=MIN(DateCol),@End=MAX(DateCol)
FROM YourTable


SELECT c.DateVal
FROM 
(SELECT DATEADD(dd,number,@Start) AS DateVal
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@Start)<=@End) c
LEFT JOIN YourTable t
ON t.DateCol=c.DateVal
WHERE t.DateCol IS NULL