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