September 16, 2010 at 1:25 pm
Hi all,
I am trying to run a query against a single table to determine hierarchy in Employee table
Employee
then Supervisor
then Manager
then VP
Now I have only the information about EMPId and its SUPERVID in my main table.
My process is:
So first I have a fact to get a set of employees and their supervisors from this table,
now after doing this, I will take distinct supvsor and get their supervisors(managers) from the same table,
Then I take distinct Managers and get again the VPs from the table,
but I require to write separate queries for this which is not coming accurate,
Is there any way to get this list from a single query using CTE's.
I am trying, but as its a common issue I think some idea will help.
Thank you
Thanks [/font]
September 16, 2010 at 2:38 pm
What should the output look like? Can you provide a sample table/data and then a sample of what the output would look like?
Also, just so that I'm clear, you have 4 strict hierarchy levels, so every employee reports to a supervisor, who reports to a manager, who reports to a VP, correct? Are there multiple VPs? Is SUPERVID null for VP's employee records?
September 16, 2010 at 2:59 pm
Here's one way to accomplish this:
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
GO
CREATE TABLE dbo.Test (
employee_id int NOT NULL PRIMARY KEY CLUSTERED,
supervisor_id int NULL);
ALTER TABLE dbo.Test ADD CONSTRAINT [FKTest] FOREIGN KEY (supervisor_id)
REFERENCES dbo.Test (employee_id);
GO
INSERT INTO dbo.Test
SELECT 1, NULL UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 7, 3 UNION ALL
SELECT 8, 3 UNION ALL
SELECT 9, 4 UNION ALL
SELECT 10,4 UNION ALL
SELECT 11,5 UNION ALL
SELECT 12,5 UNION ALL
SELECT 13,6 UNION ALL
SELECT 14,6 UNION ALL
SELECT 15,7 UNION ALL
SELECT 16,7 UNION ALL
SELECT 17,8 UNION ALL
SELECT 18,8 UNION ALL
SELECT 19,9 UNION ALL
SELECT 20,9;
-- show the data in the table
SELECT * FROM dbo.Test;
-- get employees under supervisor
DECLARE @supervisor_id int;
set @supervisor_id = 3;
WITH CTE AS
(
SELECT employee_id, supervisor_id, Level=1
FROM dbo.Test
WHERE supervisor_id = @supervisor_id
UNION ALL
SELECT t.employee_id, t.supervisor_id, CTE.Level + 1
FROM dbo.Test t
JOIN CTE ON t.supervisor_id = CTE.employee_id
)
SELECT * FROM CTE;
-- get supervisors above employee
DECLARE @employee_id INT;
set @employee_id = 17;
WITH CTE AS
(
SELECT employee_id, supervisor_id, Level = 1
FROM dbo.Test
WHERE employee_id = @employee_id
UNION ALL
SELECT t.employee_id, t.supervisor_id, CTE.Level + 1
FROM dbo.Test t
JOIN CTE ON t.employee_id = CTE.supervisor_id
)
SELECT * FROM CTE;
Edit: for help in how to post data to HELP US HELP YOU, please read the first two links in my signature.
Edit2: split SQL 2008 specific declaration/assignment into separate statements.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 9:57 pm
SSISLearner (9/16/2010)
Is there any way to get this list from a single query using CTE's.
The query that Wayne wrote will certainly do the "single query" thing correctly. Just be aware that the recursive CTE in this case uses something like 8 to 10 times the number of reads that a good ol' fashioned "lasagne loop" would use and that the loop, in this case, is also quite a bit faster than the recursive CTE (which is also Hidden RBAR).
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply