November 7, 2013 at 7:36 am
Hi
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
here is the query to display all the employees
WITH DirectReports (ManagerID, EmployeeID, Title, Level)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title,
0 AS Level
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT d.ManagerID, d.EmployeeID, d.Title, Level
FROM DirectReports as d
--WHERE d.EmployeeID = 285 OR Level = 0
GO
What i want when i pass employee id = 285 then i want to display the result of employeID = 273,1,285
His manager and manger's manager and his
ManagerIDEmployeeIDTitle Level
NULL 1Chief Executive Officer0
1 273Vice President of Sales1
273 285Pacific Sales Manager2
thank you
November 7, 2013 at 8:05 am
If I understand the question - this should get you a path - you may want to modify the ordering etc.
WITH DirectReports AS
(
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,0 AS LEVEL
,cast(e.ManagerID AS varchar(MAX)) AS [path]
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,LEVEL + 1
,cast(iif(d.[path] is not null,concat(d.[path],','),'') + cast(e.ManagerID AS varchar(MAX)) AS varchar(MAX))
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT
d.ManagerID
,d.EmployeeID
,d.Title
,LEVEL
,path
FROM DirectReports AS d
--WHERE d.EmployeeID = 285 OR Level = 0
November 7, 2013 at 8:18 am
randal.matthew (11/7/2013)
If I understand the question - this should get you a path - you may want to modify the ordering etc.
WITH DirectReports AS
(
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,0 AS LEVEL
,cast(e.ManagerID AS varchar(MAX)) AS [path]
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,LEVEL + 1
,cast(iif(d.[path] is not null,concat(d.[path],','),'') + cast(e.ManagerID AS varchar(MAX)) AS varchar(MAX))
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT
d.ManagerID
,d.EmployeeID
,d.Title
,LEVEL
,path
FROM DirectReports AS d
--WHERE d.EmployeeID = 285 OR Level = 0
I don't want the path when i pass EmployeID = 285 i want the results of his manager i.e 273 then 273's manger i.e 1
so finally i want the result of employeid 285,273,1
November 7, 2013 at 8:33 am
One more try -
WITH DirectReports AS
(
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,0 AS LEVEL
,cast(e.ManagerID AS varchar(MAX)) AS [path]
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,LEVEL + 1
,cast(cast(e.ManagerID AS varchar(MAX)) + iif(d.[path] is not null,concat(',',d.[path]),'') AS varchar(MAX))
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT
d.ManagerID
,d.EmployeeID
,d.Title
,LEVEL
,concat(d.employeeid,iif([path] is not null,concat(',',path),''))
FROM DirectReports AS d
WHERE d.EmployeeID = 285 OR Level = 0
November 7, 2013 at 8:44 am
Excellent job posting ddl and sample data. I wish everyone posted everything like that to make it easy!!!
Something like this should work.
declare @EmployeeID int = 285;
with EmployeeTree as
(
select e.ManagerID, e.EmployeeID, e.Title, e.FirstName, e.LastName, 0 as Depth
from MyEmployees e
where e.EmployeeID = @EmployeeID
union all
Select e.ManagerID, e.EmployeeID, e.Title, e.FirstName, e.LastName, Depth + 1
from MyEmployees e
join EmployeeTree et on et.ManagerID = e.EmployeeID
)
select ManagerID, EmployeeID, Title, FirstName, LastName, max(Depth) over() - Depth + 1 as CorrectDepth
from EmployeeTree
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2013 at 9:54 pm
randmatt (11/7/2013)
One more try -
WITH DirectReports AS
(
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,0 AS LEVEL
,cast(e.ManagerID AS varchar(MAX)) AS [path]
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
SELECT
e.ManagerID
,e.EmployeeID
,e.Title
,LEVEL + 1
,cast(cast(e.ManagerID AS varchar(MAX)) + iif(d.[path] is not null,concat(',',d.[path]),'') AS varchar(MAX))
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT
d.ManagerID
,d.EmployeeID
,d.Title
,LEVEL
,concat(d.employeeid,iif([path] is not null,concat(',',path),''))
FROM DirectReports AS d
WHERE d.EmployeeID = 285 OR Level = 0
only employeid 1 and 285 is coming ...it missing 273
November 7, 2013 at 9:56 pm
Sean Lange (11/7/2013)
Excellent job posting ddl and sample data. I wish everyone posted everything like that to make it easy!!!Something like this should work.
declare @EmployeeID int = 285;
with EmployeeTree as
(
select e.ManagerID, e.EmployeeID, e.Title, e.FirstName, e.LastName, 0 as Depth
from MyEmployees e
where e.EmployeeID = @EmployeeID
union all
Select e.ManagerID, e.EmployeeID, e.Title, e.FirstName, e.LastName, Depth + 1
from MyEmployees e
join EmployeeTree et on et.ManagerID = e.EmployeeID
)
select ManagerID, EmployeeID, Title, FirstName, LastName, max(Depth) over() - Depth + 1 as CorrectDepth
from EmployeeTree
Thanks a lot...this query works
November 8, 2013 at 7:17 am
SQL006 (11/7/2013)
Sean Lange (11/7/2013)
Excellent job posting ddl and sample data. I wish everyone posted everything like that to make it easy!!!Something like this should work.
declare @EmployeeID int = 285;
with EmployeeTree as
(
select e.ManagerID, e.EmployeeID, e.Title, e.FirstName, e.LastName, 0 as Depth
from MyEmployees e
where e.EmployeeID = @EmployeeID
union all
Select e.ManagerID, e.EmployeeID, e.Title, e.FirstName, e.LastName, Depth + 1
from MyEmployees e
join EmployeeTree et on et.ManagerID = e.EmployeeID
)
select ManagerID, EmployeeID, Title, FirstName, LastName, max(Depth) over() - Depth + 1 as CorrectDepth
from EmployeeTree
Thanks a lot...this query works
You are quite welcome. Do you understand why this one works and yours didn't?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2013 at 1:39 am
You are quite welcome. Do you understand why this one works and yours didn't?
What i understand from the MSDN, the anchor query which returns the input EmployeeID(base result) then the recursive query return the result based onthe anchor result set until it return the empty set.
In the anchor query it first returns -- 285 then in recursive query based on the join between the CTE managerID and employee Employee id it will parse the result set.
Hope this is a correct clarification, if not please explain it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply