Find Out the Missing Segments




CREATE TABLE  dbo.LineSample(Line_ID INT, BegMeasure numeric(6,0), Endmeasure numeric(6,0))
CREATE TABLE dbo.CodeSample(Line_ID INT, BegMeasure numeric(6,0), Endmeasure numeric(6,0),Code varchar(100))

INSERT INTO  dbo.LineSample(Line_ID, BEgMeasure,EndMeasure)

 

SELECT 656,0,254500
UNION
SELECT 657,0,7000
UNION
SELECT 658,0,308000
UNION
SELECT 659,0,20000


INSERT INTO CodeSample(Line_ID,BegMeasure,EndMeasure,Code)
 

SELECT 659,665,9456,'APL-X1'

-- 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),
Num3 (num) AS (SELECT 1 FROM Num2 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num) FROM Num3)
--1-1030301
 
,MissingMeasures As
(
Select Line_ID,  num, null as Code, 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, Cast(EndMeasure as Int) as EndMeasure from  dbo.LineSample) t )a
Where   a.num <= a.EndMeasure And Not Exists(Select * From CodeSample b Where a.Line_ID=b.Line_ID and a.num > b.BegMeasure And  a.num <= b.EndMeasure)
 
 
)
,finalCTE as (

Select Line_ID, Min(m.num)-1 As BeginMeasure, Max(m.num) As EndMeasure 
 
 
From MissingMeasures m
Group By  Line_ID,  m.num - m.SeqNbr, Code)

Select Line_ID,   BeginMeasure,   EndMeasure  
from finalCTE
--WHERE BeginMeasure<>EndMeasure
Order by Line_ID


drop table LineSample,CodeSample

/*
Line_ID	BeginMeasure	EndMeasure
656	0	254500
657	0	7000
658	0	308000
659	0	665
659	9456	20000

*/

 

https://social.msdn.microsoft.com/Forums/en-US/8070f6c8-cfd4-457a-8885-76876fb6034c/find-out-the-missing-segments?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