Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Finding  the missing Gaps Puzzle ]

The puzzle is simple. Here you have to find the missing gaps between sequential numbers. Please check out the sample input and expected output for details.

Sample Input

num
1
2
3
6
8
11
12
13
15
17
28

Expected output

GapStart GapEnd
4 5
7 7
9 10
14 14
16 16
18 27

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE table findGaps (num int)

insert into findGaps
select 1 union all
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 11 union all
select 12 union all
select 13 union all
select 15 union all
select 17 union all
select 28

GO

CREATE CLUSTERED INDEX Ix_Num ON findGaps(num)

UPDATE – 11-Apr-2015 – Solution 1


--

--Sol 1

SELECT GapStart + 1 GapStart , GapEnd - 1 GapEnd FROM 
(
	SELECT num GapStart , LEAD(num,1,0) OVER (ORDER BY Num) GapEnd , LEAD(num,1,0) OVER (ORDER BY Num) - num Gap 
	FROM findGaps 
) a
WHERE Gap > 1

--

Execution Plan below – Its a clean plain execution plan with some simple maths calculation.

Execution Plan

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements