Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This problem is all about identifying all the employees directly or indirectly to whom the given Employee reports to. The challenge is to write a query that can take a Employee Id as a parameter and list all the employees to whom the Employee is reporting, directly or indirectly.

TSQL Beginners Challenge 17- Working with Employee hierarchical structures

Solution to TSQL Beginners Challenge 17

Jan 28 2011 4:52PM by Craig   

Solution to TSQL Beginners Challenge 17

DECLARE @Employees TABLE(EmpRecord XML)
INSERT INTO @Employees 
SELECT '
		
		
		
		
		
					
		
		
		
		
		'

DECLARE @EmpID VARCHAR(20)
SELECT @EmpID = 9

;WITH MyCte (EmpId, EmpName, ReportsTo, Path, NCount) AS
(
	SELECT 
		Emp.Node.value('@EmpId', 'nvarchar(50)') EmpId,
		Emp.Node.value('@EmpName', 'nvarchar(50)') EmpName,
		Emp.Node.value('@ReportsTo', 'nvarchar(50)') ReportsTo,
		CAST(Emp.Node.value('@EmpName', 'nvarchar(50)') AS VARCHAR(50)) AS Path,
		0 AS NCount
	FROM  @Employees CROSS APPLY EmpRecord.nodes('//Employees/Employee') AS Emp(Node)
	WHERE
		Node.value('@EmpId', 'nvarchar(50)') = @EmpID
	UNION ALL
	SELECT 
		Emp.Node.value('@EmpId', 'nvarchar(50)'),
		Emp.Node.value('@EmpName', 'nvarchar(50)') ,
		Emp.Node.value('@ReportsTo', 'nvarchar(50)'),
		CAST(Path  +  '/' + Emp.Node.value('@EmpName', 'nvarchar(50)') AS VARCHAR(50)),
		NCount + 1
	FROM 
		@Employees CROSS APPLY EmpRecord.nodes('//Employees/Employee') AS Emp(Node) 
		JOIN MyCte M ON m.ReportsTo = Emp.Node.value('@EmpId', 'nvarchar(50)')
)
SELECT
	Row_Number() OVER(ORDER BY NCount DESC) - 1 AS Level,
	Path AS Path,
	Space(Row_Number() OVER(ORDER BY NCount DESC) - 1) + EmpName AS Hierarchy
FROM
	MyCte

Tags:


Craig
414 · 0% · 98
0
Liked



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]