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!
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
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 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
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
/* 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
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
order by orderby;
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.
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!