Tags

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


SQL PUZZLE | How to Select/Generate the numbers between two numbers? | SQL Interview Question

In this puzzle you have to select the numbers between two numbers. Please check the sample input and expected output for more details

Sample Input

Id St Et
1 2 7
2 10 15
3 16 18

Expected Output

Id St Et Number
1 2 7 2
1 2 7 3
1 2 7 4
1 2 7 5
1 2 7 6
1 2 7 7
2 10 15 10
2 10 15 11
2 10 15 12
2 10 15 13
2 10 15 14
2 10 15 15
3 16 18 16
3 16 18 17
3 16 18 18

Script – DDL and INSERT Script

--

CREATE TABLE Nums
(
	 Id INT
	,St INT
	,Et INT
)
GO

INSERT INTO Nums VALUES
(1,2,7),
(2,10,15),
(3,16,18)
GO

SELECT * FROM Nums
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION 1

--


;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
SELECT * FROM Nums N
INNER JOIN Series s ON s.Number >= N.St AND s.Number <= N.Et
ORDER BY Id,Number


--

OUTPUT – 1

--


Id          St          Et          Number
----------- ----------- ----------- -----------
1           2           7           2
1           2           7           3
1           2           7           4
1           2           7           5
1           2           7           6
1           2           7           7
2           10          15          10
2           10          15          11
2           10          15          12
2           10          15          13
2           10          15          14
2           10          15          15
3           16          18          16
3           16          18          17
3           16          18          18

(15 rows affected)



--

SOLUTION 2

--

SELECT DISTINCT N.* , S.number FROM master..spt_values s
INNER JOIN Nums N ON s.number >= N.St AND s.Number <= N.Et
WHERE s.number > 0
ORDER BY Id,s.Number

--

OUTPUT

--


Id          St          Et          number
----------- ----------- ----------- -----------
1           2           7           2
1           2           7           3
1           2           7           4
1           2           7           5
1           2           7           6
1           2           7           7
2           10          15          10
2           10          15          11
2           10          15          12
2           10          15          13
2           10          15          14
2           10          15          15
3           16          18          16
3           16          18          17
3           16          18          18

(15 rows affected)


--

Please add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn. Thanks in advance.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com