Finding Missing Numbers From a Range




Declare @Lines table (Line_ID int,  BeginMeasure  int,  EndMeasure int)
Insert into @Lines values(1,0,100),(2,0,200),(3,0,300)

Declare @codes  Table(Line_ID int, BeginMeasure int, EndMeasure int);
Insert @codes(Line_ID, BeginMeasure, EndMeasure) Values(1, 12,30),(1,40,50),(2,0,150),(3,30,300)
 
-- Create Numbers cte
;WITH Num1 (num) AS (
SELECT 1 as num
UNION ALL SELECT num+1 
FROM Num1 Where num<101),
Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num)-1 FROM Num2),
------1-10201
MissingMeasures As
(
Select Line_ID,  num, Row_Number() Over (partition by Line_ID  Order By num) As SeqNbr from (Select t.Line_ID, t.EndMeasure, n.num
From Nums n cross join (select Line_ID, EndMeasure from @Lines) t )a
Where   a.num <= a.EndMeasure And Not Exists(Select * From @codes b Where a.Line_ID=b.Line_ID and a.num Between b.BeginMeasure And b.EndMeasure)


)

Select Line_ID, Min(m.num) As BeginMeasure, Max(m.num) As EndMeasure


From MissingMeasures m
Group By  Line_ID,  m.num - m.SeqNbr
Order by Line_ID



/*
Line_ID	BeginMeasure	EndMeasure
1	1	11
1	31	39
1	51	100
2	151	200
3	1	29

*/



 

http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx

http://social.msdn.microsoft.com/Forums/en-US/a16581eb-2ccd-4df4-959d-d687583564a4/finidng-the-missing-segments-and-return-to-the-output?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s