SQL: Common Table expression (CTE)

Posted by Sudheep.Grandhe under Sql Server on 12/11/2014 | Points: 10 | Views : 432 | Status : [Member] | Replies : 3
Hi Team,

i didn't understand, EmployeeLevel + 1 works in the below code.

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);


;WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)


Can u tell me, how the EmployeeLevel + 1 works in CTE

Regards,
Sudheep.G

Best,
Sudheep.



Responses

Posted by: Ogipansrk on: 12/12/2014 [Member] Starter | Points: 25

Up
0
Down
what is the issue ?,above query looks fine

Sudheep.Grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.Grandhe on: 12/12/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

Thanks for replying. I am having a doubt of how the EmployeeLevel + 1 works in CTE.



Best,
Sudheep.

Sudheep.Grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/15/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,

the query is doing recurive process to fetch employees hierarchy..

Read below article to get clarity on the process
http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sudheep.Grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response