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


–Option 1: Find missing values with integer range (List missing values)
Declare @t Table(id int identity(1,1), FromValue int, ToValue int)
Insert into @t
Values (11,20),(10,20),(1,6), (18,21), (11,20), (40,50), (19,35)

Declare @MaxToVal int
–Get the max ending value
SELECT @MaxToVal=max(Tovalue) FROM @t;

–Return all sequence–no gap from min to max values
;with mycteAll
as
(
Select min(FromValue) as val FROM @t
Union ALL
SELECT val + 1 as val FROM mycteAll WHERE val <@MaxToVal
)

,

mycte as
(
SELECT id, FromValue 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

)

SELECT a.val AS val FROM mycteAll a
LEFT JOIN (SELECT distinct val FROM mycte) b ON a.val=b.val
WHERE b.val IS NULL

—-overwrite the default maximum recursion 100
OPTION (MAXRECURSION 0)

/* Result
7
8
9
36
37
38
39
*/
–Option 2: Find missing values with integer range (list missing range)
Declare @t Table(id int identity(1,1), FromValue int, ToValue int)
Insert into @t
Values (11,20),(10,20),(1,6), (18,21), (11,20), (40,50), (19,35)

Declare @MaxToVal int
–Get the max ending value
SELECT @MaxToVal=max(Tovalue) FROM @t;

–Return all sequence–no gap from min to max values
;with mycteAll
as
(
Select min(FromValue) as val FROM @t
Union ALL
SELECT val + 1 as val FROM mycteAll WHERE val <@MaxToVal
)

,

mycte as
(
SELECT id, FromValue 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

)
, cteResult as (
SELECT a.val AS val, row_number() OVER (order by a.val) rn
FROM mycteAll a
LEFT JOIN (SELECT val FROM mycte) b ON a.val=b.val
WHERE b.val IS NULL)

Select min(val) as sValue, max(val) as eValue from cteResult
group by val-rn

—-overwrite the default maximum recursion 100
OPTION (MAXRECURSION 0)

/* Result
7 9
36 39
*/

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