Tags

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


SQL Puzzle | Find Grand Parent, Parent and Child Puzzle ( Parent/Child relationship tree between Range )

In this puzzle the requirement is to generate the range and level column. E.g. 1-100 is grandfather RANGE is 0 and level is 1, 2-50 is the 1st son so range column value will be 144 and level will be 1 and so on..

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 0 1
145 2 50 144 2
146 8 25 145 3
147 51 70 144 2
148 57 60 147 3
164 200 250 0 1
172 201 225 164 2
174 201 213 172 3
188 310 350 0 1
194 333 333 188 2

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


--
 
;WITH CTE AS
(	
	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
)
,CTE3 AS 
(
	SELECT a.id , a.start_value , a.end_vaule , 0 [range] , 1 [level] FROM CTE a
	UNION ALL  
	SELECT t.id , t.start_value , t.end_vaule , c.id , c.[level] + 1 [level]
	FROM CTE3 c INNER JOIN Testlevels t ON ( c.start_value <= t.start_value AND c.end_vaule > t.end_vaule )		
)
SELECT id , start_value , end_vaule , [range], [level] FROM
(
   SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY range desc,level desc) rnk FROM CTE3 
)X 
Where rnk = 1
ORDER BY id

--

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