How to find Manager and Employee Level in SQL Server

In this article we will see how CTE can be used to get manager information and its employee level.

 As CTE can be used in recursive and no-recursive queries. We will use recursive approach to get desired result

 1. First create table with below structure

 IF OBJECT_ID('tEmployees', 'U') IS NOT NULL
DROP TABLE dbo.tEmployees
GO
CREATE TABLE dbo.tEmployees
(
  EmployeeID int NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  ManagerID int NULL
)
GO

 2. Insert some records into

 INSERT INTO tEmployees VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO tEmployees VALUES (102, 'Terri', 'Duffy', 101)
INSERT INTO tEmployees VALUES (103, 'Roberto', 'Tamburello', 101)
INSERT INTO tEmployees VALUES (104, 'Rob', 'Walters', 102)
INSERT INTO tEmployees VALUES (105, 'Gail', 'Erickson', 102)
INSERT INTO tEmployees VALUES (106, 'Jossef', 'Goldberg', 103)
INSERT INTO tEmployees VALUES (107, 'Dylan', 'Miller', 103)
INSERT INTO tEmployees VALUES (108, 'Diane', 'Margheim', 105)
INSERT INTO tEmployees VALUES (109, 'Gigi', 'Matthew', 105)
INSERT INTO tEmployees VALUES (110, 'Michael', 'Raheem', 106)

 3. Create Select queries using CTE

 WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM tEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM tEmployees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT
  FirstName + ' ' + LastName AS FullName,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM tEmployees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

 4. See desired result

5. References:
https://msdn.microsoft.com/en-IN/library/ms175972.aspx
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

Comments

Post a Comment

Popular posts from this blog

How to Add HttpModule in MVC5 Application

Image
An HTTP module is an assembly that is called on every request made to your application. HTTP modules are called as part of the ASP.NET request pipeline and have access to life cycle events throughout the request. HTTP modules therefore give you the opportunity to examine incoming requests and take action based on the request. They also give you the opportunity to examine the outbound response and modify it. As MVC application is built over ASP.NET so we can create custom HttpModule in MVC application also. In this article we will create a simple HttpModule and register it in HttpApplication pipeline and verify that is loaded. Create a LoginModule Class in your MVC Application.   For creating HttpModule we have implement  IHttpModule interface which have two functions Dispose and Init. We have implemented Init method here. 2 .         In ASP.NET we were registering HttpModule in Web.Config. We can register module in MVC using Global.asax. Bel...
Read more

Map Routes using Route Class in MVC

In MVC application, we can register routes in RouteCollection using RouteConfig . This RouteConfig is called using Application_Start of application. RouteConfig is configured as below routes.MapRoute(                 name: "Default" ,                 url: "{controller}/{action}/{id}" ,                 defaults: new { controller = "ProgramConfigEditor" , action = "Index" , id = UrlParameter .Optional }             ); We can register route of the application using Route Class as below. After creation of Route collection, it will move to MvcRouteHandler object. Route route = new Route ( "{controller}/{action}/{id}" ,           ...
Read more

How to convert a string with multiple comma separated values to rows in SQL Server

Let’s we have below table structure: create table Testdata ( SomeID int , OtherID int , Data varchar ( max ) ) This table contain below sample records insert Testdata select 1 , 9 , '18,20,22' insert Testdata select 2 , 8 , '17,19' insert Testdata select 3 , 7 , '13,19,20' insert Testdata select 4 , 6 , '' We want to get result like 1 9 18 1 9 20 1 9 22 2 8 17…. We can use below two options for this problem. Using function and table variable  CREATE FUNCTION dbo . Split (     @Line nvarchar ( MAX ),     @SplitOn nvarchar ( 5 ) = ',' ) RETURNS @RtnValue table (     Id INT NOT NULL IDENTITY ( 1 , 1 ) PRIMARY KEY CLUSTERED ,     Data nvarchar ( 100 ) NOT NULL ) AS BEGIN     IF @Line IS NULL RETURN     DECLARE @split_on_len INT = LEN ( @SplitOn )   ...
Read more