DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply recursive queries for organizational charts?

Execute the following Microsoft SQL Server T-SQL recursive scripts in Management Studio Query Editor to get the "immediate supervisor" organizational chart of AdventureWorks Cycles and the number of subordinates at each level.

-- SQL recursive Common Table Expression - CTE - tree processing, tree parsing

-- MSSQL organizational chart - orgchart - direct report - chain of command

USE AdventureWorks;

GO

WITH cteSupervisor(ManagerID,EmployeeID,EmployeeLevel)

     AS (SELECT ManagerID,

                EmployeeID,

                EmployeeLevel = 0

         FROM   HumanResources.Employee

         WHERE  ManagerID IS NULL

         UNION ALL

         SELECT e.ManagerID,

                e.EmployeeID,

                EmployeeLevel + 1

         FROM   HumanResources.Employee e

                INNER JOIN cteSupervisor d

                  ON e.ManagerID = d.EmployeeID)

SELECT Manager = CO.LastName + ', ' + CO.FirstName,

       Employee = C.LastName + ', ' + C.FirstName,

       EmployeeLevel

FROM   cteSupervisor DR

      INNER JOIN HumanResources.Employee E

         ON DR.EmployeeID = E.EmployeeID

      INNER JOIN Person.Contact C

         ON E.ContactID = C.ContactID

      LEFT JOIN HumanResources.Employee EM

         ON DR.ManagerID = EM.EmployeeID

      LEFT JOIN Person.Contact CO

         ON EM.ContactID = CO.ContactID

GO

-- Results

Manager Employee EmployeeLevel
NULL Sánchez, Ken 0
Sánchez, Ken Bradley, David 1
Sánchez, Ken Duffy, Terri 1
Sánchez, Ken Trenary, Jean 1
Sánchez, Ken Norman, Laura 1
Sánchez, Ken Hamilton, James 1
Sánchez, Ken Welcker, Brian 1
Welcker, Brian Jiang, Stephen 2
Welcker, Brian Alberts, Amy 2
Welcker, Brian Abbas, Syed 2
Abbas, Syed Tsoflias, Lynn 3
Alberts, Amy Pak, Jae 3
Alberts, Amy Varkey Chudukatil, Ranjit 3
Alberts, Amy Valdez, Rachel 3

....

------------

 

-- T-SQL count descendants at each level - count subordinates in organization

-- Parent-child hierarchy - count children by level

DECLARE  @EmployeeID INT,  @Supervisor NVARCHAR(50)

 

DECLARE curEmployee CURSOR  FOR

SELECT EmployeeID, FullName = FirstName + ' ' + LastName

FROM   AdventureWorks.HumanResources.Employee e

       INNER JOIN AdventureWorks.Person.Contact pc

         ON pc.ContactID = e.ContactID

 

DECLARE  @Descendant  TABLE(

                            EmployeeID        INT,

                            FullName          NVARCHAR(50),

                            [Level]           INT    NULL,

                            [DescendantCount] INT    NULL

                            )

OPEN curEmployee

FETCH NEXT FROM curEmployee

INTO @EmployeeID, @Supervisor

 

WHILE (@@FETCH_STATUS = 0 )

  BEGIN

    WITH cteSubTree

         AS (SELECT EmployeeID, 0 AS [Level]

             FROM   AdventureWorks.HumanResources.Employee e

             WHERE  EmployeeID = @EmployeeID -- root

             UNION ALL

             SELECT e.EmployeeID, [Level] + 1 -- recursive term

             FROM   cteSubTree c

                    INNER JOIN AdventureWorks.HumanResources.Employee e

                      ON c.EmployeeID = e.ManagerID)

    INSERT INTO @Descendant

    SELECT   @EmployeeID,

             @Supervisor,

             [Level],

             COUNT(* )  

    FROM     cteSubTree

    GROUP BY [Level]

    FETCH NEXT FROM curEmployee

    INTO @EmployeeID,  @Supervisor

  END

 

SELECT   FullName,

         [Level],

         DescendantCount,

         t.EmployeeID

FROM     @Descendant t

         INNER JOIN (SELECT   EmployeeID

                     FROM     @Descendant

                     GROUP BY EmployeeID

                     HAVING   count(* ) > 1) g

           ON t.EmployeeID = g.EmployeeID

ORDER BY t.EmployeeID, [Level]

CLOSE curEmployee

DEALLOCATE curEmployee

GO

/*

FullName                Level DescendantCount   EmployeeID

Roberto Tamburello      0     1                 3

Roberto Tamburello      1     7                 3

Roberto Tamburello      2     5                 3

David Bradley           0     1                 6

David Bradley           1     8                 6

JoLynn Dobney           0     1                 7

JoLynn Dobney           1     6                 7

....

*/

------------

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE