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 total number of missing numbers from the table.
Input Data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Number ----------- 1 2 3 5 6 8 9 15 19 21 22 24 29 |
Expected Data:
1 2 3 |
TotalMissingNumbers -------------------- 16 |
Create a table with data:
1 2 3 4 5 6 |
CREATE TABLE tbl_Ranges (Number INTEGER PRIMARY KEY) GO INSERT INTO tbl_Ranges VALUES (1),(2),(3),(5),(6),(8),(9),(15),(19),(21),(22),(24),(29) GO |
Solution:
1 2 3 4 5 6 7 8 9 10 |
;WITH CTE AS ( SELECT Number ,Number - row_number() OVER (ORDER BY Number) rnk FROM tbl_Ranges ) SELECT MAX(rnk) TotalMissingNumbers FROM CTE |
Please try the different solution for this puzzle and share it via comment...