Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

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:

Smith
Lana
Johnson

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:

  1. The query should run on SQL Server 2005 and 2008.
  2. Write a single query that returns the results. No temp tables, functions etc should be used.
  3. Use 4 spaces for indentation while generating the output hierarchy.
  4. 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)
  5. Visit this forum for general questions on TSQL Challenges.
  6. Visit this forum for any question on TSQL Challenge 8.

Share

Comments

# re: TSQL Challenge 8

Wednesday, May 27, 2009 10:05 AM by Cleber

So why would I use CTE if a simple query can do it?


# re: TSQL Challenge 8

Wednesday, May 27, 2009 10:12 AM by Jacob Sebastian

A CTE is considered to be part of the query that it follows. So, if you use a CTE it is acceptable.


# re: TSQL Challenge 8

Thursday, May 28, 2009 5:55 AM by Divya Agrawal

Is it necessary to show the reportsto empname for the employees indirectly related to the manager.

For eg.

If the parameter passed is ‘Jacob’, should the query show the reportsto emp for John as Steve or just a list of all the employess as:

Jacob

  Jess

  Steve

  John

  Bob

  Michael

  Paul


# re: TSQL Challenge 8

Thursday, May 28, 2009 6:35 AM by Jacob Sebastian

The result should be indented to show a hierarchy


# re: TSQL Challenge 8

Saturday, April 10, 2010 10:03 AM by NetDefender

Funny


Copyright © Rivera Informatic Private Ltd.