TSQL Challenge 8
Welcome to TSQL Challenge 8!
This challenge is more about processing HIERARCHIES. SQL Server 2008 introduced a new data type HIERARCHYID which is pretty good for processing hierarchies. However, the problem explained in this challenge should be fixed without using the HIERARCHYID data type.
The Problem
The problem is all about identifying all the employees directly or indirectly reporting to a given manager. Here is the organization chart of my fictitious company.
/*
Jacob
Jess
Steve
John
Bob
Michael
Paul
Rui
Smith
Lana
Johnson
Bobbey
Mic
Stev
Jacobson
Steffi
Paulson
Bracha
Jessica
*/
The Challenge
The challenge is to write a query that can take a Manager Name as a parameter and list all the employees reporting to that manager, directly or indirectly. If the parameter contains “Smith” the query should return:
If the parameter passed is ‘Jacob’, the query should return:
Jacob
Jess
Steve
John
Bob
Michael
Paul
Your query should ideally look like the following:
DECLARE @manager VARCHAR(20)
SELECT @manager = 'Smith'
-- Your query here:
Adding Complexity
This query is pretty easy to write using a recursive CTE. To make the query complex, so that it will meet the complexity level expected for the ‘TSQL Challenges’ series, I am adding the following restriction.
“The query should be written using a recursive CTE, but the filter for "@manager” should not be applied inside the CTE”
Sample Data
Use the following sample data for writing your queries:
DECLARE @Employees TABLE (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)
INSERT INTO @Employees(EmpID, EmpName, ReportsTo)
SELECT 1, 'Jacob', NULL UNION ALL
SELECT 2, 'Rui', NULL UNION ALL
SELECT 3, 'Jacobson', NULL UNION ALL
SELECT 4, 'Jess', 1 UNION ALL
SELECT 5, 'Steve', 1 UNION ALL
SELECT 6, 'Bob', 1 UNION ALL
SELECT 7, 'Smith', 2 UNION ALL
SELECT 8, 'Bobbey', 2 UNION ALL
SELECT 9, 'Steffi', 3 UNION ALL
SELECT 10, 'Bracha', 3 UNION ALL
SELECT 11, 'John', 5 UNION ALL
SELECT 12, 'Michael', 6 UNION ALL
SELECT 13, 'Paul', 6 UNION ALL
SELECT 14, 'Lana', 7 UNION ALL
SELECT 15, 'Johnson', 7 UNION ALL
SELECT 16, 'Mic', 8 UNION ALL
SELECT 17, 'Stev', 8 UNION ALL
SELECT 18, 'Paulson', 9 UNION ALL
SELECT 19, 'Jessica', 10
Notes:
- The query should run on SQL Server 2005 and 2008.
- Write a single query that returns the results. No temp tables, functions etc should be used.
- Send your entries to tc@beyondrelational.com with subject ‘TSQL Challenge 8’ and send your code as an attachment (.sql file)
- Use 4 spaces for indentation while generating the output hierarchy.
- Siblings within a parent node may be sorted either by Name or by ID (I have sorted them by ID in the sample given above)
- Last date to submit your entries: 1 June 2009
- Visit this forum for general questions on TSQL Challenges.
- Visit this forum for any question on TSQL Challenge 8.
Get notified when a new challenge is available or the evaluation result published either by subscribing to the 
RSS feed or subscribing to the Email Notification.