How to calculate the number of people reporting and remove their list MS SQL



  • Indicate, the number of people on the line of command must be counted and their list removed. Employees.ReportsTo indicates idto whom this man reports.

    Select
    

    Employees.LastName,

    Employees.FirstName,

    Region.RegionDescription,

    count(case when Employees.ReportsTo=Employees.EmployeeID then 1 end) as Count_of_employees,

    Subordinates = STUFF((
    SELECT ', ' + Employees.LastName
    FROM Employees
    WHERE Employees.ReportsTo = Employees.EmployeeID
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

    from Employees

    left join EmployeeTerritories on

    Employees.EmployeeID=EmployeeTerritories.EmployeeID

    left join Territories on

    EmployeeTerritories.TerritoryID=Territories.TerritoryID

    left join Region on Territories.RegionID=Region.RegionID

    group by Region.RegionDescription,Employees.LastName, Employees.FirstName



  • You need to connect the table to yourself, pointing out the different references to the table from which you take the staff list and the table from which you take the list of subordinates.

    Like this:

    Select  empl.LastName,
            empl.FirstName,
            Region.RegionDescription,
            count(empl1.EmployeeID) as Count_of_employees,
            Subordinates = STUFF((
              SELECT ', ' + empl2.LastName
              FROM Employees empl2
              WHERE empl2.ReportsTo = empl.EmployeeID
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    

    from Employees empl
    left join Employees empl1
    on empl1.ReportsTo=empl.EmployeeID

    left join EmployeeTerritories on

    empl.EmployeeID=EmployeeTerritories.EmployeeID

    left join Territories on

    EmployeeTerritories.TerritoryID=Territories.TerritoryID

    left join Region on Territories.RegionID=Region.RegionID

    group by Region.RegionDescription,empl.EmployeeID, empl.LastName, empl.FirstName




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2