Tags
Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Divide rows into two columns, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ The Complex Week Puzzle ], The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The Tree Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle
T-SQL Query | [ The Gap Puzzle – II ]
Puzzle Description
1. We have a table called FindGaps with a single column col1.
2. We have to find out the gaps between these numbers in two columns like when the Gap starts and when the gap ends.
3. Please check out the sample input and expected output for details
Sample Input
| Col1 |
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 9 |
| 11 |
| 15 |
| 17 |
Expected Output
| GapStart | GapEnd |
| 2 | 2 |
| 8 | 8 |
| 10 | 10 |
| 12 | 14 |
| 16 | 16 |
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 ( Col1 INT ) GO INSERT INTO FindGaps(Col1) VALUES (1),(3),(4),(5),(6),(7),(9),(11),(15),(17) CREATE CLUSTERED INDEX Ix_Gaps ON FindGaps(Col1) --
Update June 16 | Solution1 – Pawan Kumar Khowal
-- ;WITH CTE AS ( SELECT COl1 ,COl1 - ROW_NUMBER() OVER (ORDER BY COl1) rnk FROM FindGaps ) ,CTE1 AS ( SELECT MIN(Col1) - 1 GStart, MAX(col1) + 1 Gend, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks FROM CTE Group By Rnk ) SELECT CASE WHEN a.GStart >= b.Gend THEN b.Gend ELSE a.GStart END GapStart, CASE WHEN a.GStart >= b.Gend THEN a.GStart ELSE b.Gend END GapEnd FROM CTE1 a INNER JOIN CTE1 b ON a.rnks-1 = b.rnks --
Add a comment if you have any other solution in mind. We all need to learn. Keep Learning
Http://MSBISkills.com
Pawan Kumar Khowal
Improving my SQL BI Skills 
looks like the provided solutions do not work correctly. I might be wrong but I have another way of resolving the puzzle as below
CREATE TABLE #Gaps(Col1 INT)
INSERT INTO #Gaps(Col1)
VALUES (1),(3),(4),(5),(6),(7),(9),(11),(15),(17),(25),(30)
WITH cte AS (
SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY name) rn
FROM master..spt_values
)
, cte1 AS (
SELECT *
FROM cte
WHERE NOT EXISTS (
SELECT *
FROM #Gaps
WHERE Col1 = cte.rn
)
)
, xx AS (
SELECT rn AS n1
, (
SELECT rn
FROM cte1
WHERE rn = a.rn + 1
) n2
FROM cte1 a
)
, x3 AS (
SELECT n1
, iif(n2 IS NULL, n1, (
SELECT TOP 1 n1
FROM xx AS z
WHERE z.n1 > xx.n1
AND n2 IS NULL
)) n2
FROM xx
)
SELECT min(n1) n1, n2
FROM x3
GROUP BY n2
ORDER BY 1
Thank you for publishing these interesting puzzles!
LikeLike
Thanks bro ! I have updated the post…
LikeLike
DECLARE @min INT =(SELECT MIN(Col1) FROM FindGaps)
DECLARE @max INT =(SELECT MAX(Col1) FROM FindGaps)
DECLARE @t1 TABLE (Col1 INT)
WHILE(@min<=@max)
BEGIN
INSERT INTO @t1
SELECT @min
SET @min=@min+1
END
SELECT
Min(col1) GapStart,
Max(Col1) GapEnd
FROM (
SELECT
t1.Col1,
t1.Col1-row_number() over(ORDER BY t1.Col1) Rn
FROM @t1 AS t1
LEFT JOIN FindGaps as t2 ON t1.Col1=t2.Col1
WHERE t2.Col1 IS NULL
) x
GROUP BY Rn
LikeLike
;with a as (
select col1, lead(col1) over (order by col1)-col1 as diff,lead(col1) over (order by col1)-1 diffend from FindGaps1
)
select col1+1 as gapstart,diffend as gapend from a where diff1
LikeLiked by 1 person