T-SQL Query | [ The Gap Puzzle – III ]

Puzzle Description

1. We have a table called FindGaps with a single column col1.
2. If we have gap of more than 2 then we will conside that as a GAP.
3. We have to find out the gaps between these numbers in two columns like when the Gap starts and when the gap ends.
4. 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

RangeStart	RangeEnd
1	11
15	17

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
	,CASE WHEN COl1 - lag(col1) OVER (ORDER BY COl1) <= 2 THEN 0 ELSE 1 END rnk
FROM FindGaps
)
,CTE2 AS
(
	SELECT COl1,SUM(rnk) OVER (ORDER BY Col1 rows unbounded preceding) grouper
	FROM CTE
)
SELECT MIN(col1) RangeStart , MAX(col1) RangeEnd FROM CTE2
GROUP BY grouper

--

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

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ The Total Values Missing Puzzle ]

Puzzle Description

1. We have a table called FindGaps with a single column col1.
2. We have to find out how many total values are missing between starting values till the last value.
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

TotalMissingValues

7

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
)
SELECT MAX(rnk) TotalMissingValues FROM CTE

--

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

Http://MSBISkills.com

Pawan Kumar Khowal

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