T-SQL to Find Gap (missing sequence item) With Range Data


<code>

Declare @t Table(id int identity(1,1), FromValue decimal(6,2), ToValue

decimal(6,2))

Declare @MaxToVal decimal(6,2)

–Load sample table data with overlap range

Insert into @t

Values (90.00 , 100.01),

(90.55 , 100.00),

(100.05 , 110.00)

–Get the max ending value

SELECT @MaxToVal=max(Tovalue) FROM @t;

–Create sequense with gap

;with mycte

as (

SELECT id, Cast(FromValue * 100 AS INT) AS val FROM @t

UNION ALL

SELECT b.id, b.val + 1 AS val

FROM mycte b INNER JOIN @t a ON a.id = b.id

WHERE val < a.ToValue * 100

)

–Return all sequence–no gap from min to max values

, mycteAll as

(

Select CAST(min(FromValue)*100 as int) as val from @t

Union ALL

SELECT val + 1 as val FROM mycteAll WHERE val <@MaxToVal*100

)

–Get the missing values for gap

–SELECT Cast(val / 100.0 AS DECIMAL(6, 2)) AS Val FROM mycteAll

–EXCEPT

–SELECT Cast(val / 100.0 AS DECIMAL(6, 2)) AS Val FROM mycte

SELECT Cast(a.val / 100.0 AS DECIMAL(6, 2)) AS Val FROM mycteAll a

LEFT JOIN mycte b ON a.val=b.val WHERE b.val IS NULL

–overwrite the default maximum recursion 100

OPTION (MAXRECURSION 0)

/* Result

100.02

100.03

100.04

*/</code>

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