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 Winners

Thanks to all Challengers

I would like to cheerfully thanks all challengers for their solutions to this challenge.
It's the members of a community that grow that community. Information you are sharing here is the very heart of this learning community's value!

The Challenge

So, challenge 8 was about processing HIERARCHIES… without using the new data type HIERARCHYID of SQL Server 2008.

Using a CTE was required 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


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, the following restriction was added:

“The query should be written using a recursive CTE, but the filter for "@manager” should not be applied inside the CTE”


For the sake of simplicity, let’s start with a filter inside the CTE.

This a Rob Farley’s code.  Rob sent this solution before this restriction on the filter for @manager was added. 
Thanks Rob for this great code and for clear comments ! 

DECLARE @Employees TABLE (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)
INSERT INTO @Employees(EmpID, EmpName, ReportsTo)
    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

DECLARE @manager VARCHAR(20)
SELECT @manager = 'Jacob'
-- Your query here:

; /* Previous query should've been ended with a semi-colon */
with OrgChart as
/* Base case for recursive CTE, with an empty varbinary to start with */
select cast('' as varbinary(max)) as orderby, e.EmpId, e.EmpName
from @Employees e
where e.EmpName = @manager

union all

/* Recursive case, padding onto the end of the varbinary to maintain correct order */
select orderby + cast(e.EmpID as varbinary(4)), e.EmpId, e.EmpName
from OrgChart o
	@Employees e
	on e.ReportsTo = o.EmpID
/* Now see the length of the orderby, divided by 4 (the length of an integer), and put this many pads in */
/* NB: As the pad is four spaces, this could've been done using a single space and no division, but I prefer it this way for flexibility */
select replicate('    ',len(orderby) / 4) + EmpName
from OrgChart
order by orderby;


Possible pittfalls

If you make a first attempt using @manager at CTE’s anchor. When getting the filter on @manager outside the CTE, do not forget to restrict CTE’s anchors to employees reporting to no one (ReportsTo IS NULL), or you’ll have a huge quantity of unnecessary reads!

Cosmetics: In the output resultset, do not pad the manager.

The Winners

Congratulations to the winners :-)

We’ll see in a few days how Matthieu, Syed and Leonid did manage this challenge.

  • Matthieu Hodin
    Matthieu Hodin is Project Manager Wygwam, developper since he  got an HP48GX in hands (who remember?), and web developper since 2002.
    Interested in "data" and "interface".
    Activity preferred at job : put MVP's at work and challenge them to find solutions (for customers and not only for these SQL challenges ;p) ) !
  • Syed Mehroz Alam
    Syed Mehroz Alam, living in Karachi, Pakistan, is primarily a developer focusing Microsoft technologies. He has completed his bachelors as a Computer Systems Engineer in 2006 and is currently pursuing a Masters degree in Computer Science. Despite developing rich internet applications, he loves to work with SQL Business Intelligence platform that enhances his TSQL expertise. He is fond of logical challenges and has won several speed programming competitions which are listed here.

    Mehroz writes articles at CodeProject, and expresses his experiences with .NET and SQL server at his blog. When he has time, he contributes to MSDN and Silverlight forums. He loves to play football (Soccer) and computer games, especially first person shooters and RPGs.
  • Leonid Koyfman

    Leonid is a ‘many times winner’ of the challenge and SQL Server Expert.   

If you have generic questions about this challenge the dedicated is still open to discuss: http://beyondrelational.com/groups/tsqlchallenge/forum/t/66.aspx 


Thanks again, and stay tuned for next challenges!


Copyright © Rivera Informatic Private Ltd.