Tags

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


SQL Puzzle | The NOT Between Puzzle

In this puzzle you have to find out the records in which start value and end value is not between any other start value and end value.
E.g. Id = 144, range 1 – 100 is not between any other value, same goes for id 164 and id = 188

Please check out the sample input values and sample expected output below.

Sample Input

id start_value end_vaule range level
144 1 100 NULL NULL
145 2 50 NULL NULL
146 8 25 NULL NULL
147 51 70 NULL NULL
148 57 60 NULL NULL
164 200 250 NULL NULL
172 201 225 NULL NULL
174 201 213 NULL NULL
188 310 350 NULL NULL
192 325 350 NULL NULL
194 333 333 NULL NULL

Expected Output

id start_value end_vaule range level
144 1 100 NULL NULL
164 200 250 NULL NULL
188 310 350 NULL NULL

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Testlevels
(
	 id INT 
	,start_value  INT 
	,end_vaule  INT
	,[range] INT
	,[level]  INT
)
GO

INSERT INTO Testlevels VALUES
(144,      1     , 100    ,  NULL   ,  NULL	   ),
(145,      2     , 50     , NULL    , NULL	   ),
(146,      8     , 25     , NULL    , NULL	   ),
(147,      51    ,  70    ,  NULL   ,  NULL	   ),
(148,      57    ,  60    ,  NULL   ,  NULL	   ),
(164,      200   ,   250  ,    NULL ,    NULL   ),
(172,      201   ,   225  ,    NULL ,    NULL   ),
(174,      201   ,   213  ,    NULL ,    NULL   ),
(188,      310   ,   350  ,    NULL ,    NULL   ),
(192,      325   ,   350  ,    NULL ,    NULL   ),
(194,      333   ,   333  ,    NULL ,    NULL   )
GO
			 
--

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

Solution 1


--
 
SELECT * 
FROM Testlevels a
EXCEPT
SELECT DISTINCT k.* FROM Testlevels a
CROSS APPLY
(
SELECT * FROM Testlevels b
WHERE 
	b.start_value >= a.start_value AND b.start_value <= a.end_vaule
AND b.end_vaule >= a.start_value and b.end_vaule <= a.end_vaule
AND b.id <> a.id	
)k

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com