This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the value Start – End range for the Gaps of given series.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 |
nums ----------- 1 2 5 6 9 16 17 18 20 |
Expected Output:
|
1 2 3 4 5 6 |
GapStart GapEnd ----------- ----------- 3 4 7 8 10 15 19 19 |
Create a table with sample data:
|
1 2 3 4 5 6 7 |
CREATE table Numbers (nums int) GO INSERT INTO Numbers VALUES (1),(2),(5),(6),(9),(16),(17),(18),(20) GO |
SQL Server 2012: Introduced LEAD function, the best alternative of Self Join to compare Next Row
Solution: Using LEAD()
|
1 2 3 4 5 6 |
SELECT GapStart + 1 GapStart , GapEnd - 1 GapEnd FROM ( SELECT nums GapStart , LEAD(nums,1,0) OVER (ORDER BY nums) GapEnd , LEAD(nums,1,0) OVER (ORDER BY nums) - nums Gap FROM Numbers ) a WHERE Gap > 1 |
Please try the different solution for this puzzle and share it via comment...
