[Challenge] 計算各經理和其部屬薪資

Beyond Relational TSQL Challenge 52

In a Employee Parent-Child dimension, where each employee has his own identification number and a parent number to his manager. This challenge is intended to calculate company's wage spent below a top level manager.

• Sample Data

Employee_Name Number ManagerNumber Wage   
------------- ------ ------------- -------
Jacob         345    NULL          3000.00
Marco         873    NULL          3000.00
John          844    345           1880.00
James         139    844           2010.00
Ruth          111    873           1550.00
Margaret      622    345           1300.00
Mike          999    622           2010.00
Paul          611    139           1400.00

• Expected Results

Name   Total Wage  Max wage amount  Max wage employees
-----  ----------  ---------------  ------------------
Jacob  11600.00    2010.00          James,Mike
Marco  4550.00     1550.00          Ruth

• Rules
1. Note that the tree is not balanced .
2. Note that there is no flag to determine if an employee is a leaf employees.
3. 'Max wage employees' is a comma separated list of people (in alphabetical order) earning that maximum wage.
4. Employee Names will contain only letters a-z in upper, lower or mixed case.
5. The output should be ordered by name in a case insensitive alphabetical order.
6. When the top level manager has no employee, the output should be 0.00 for the max wage amount and an empty string for the max wage employees.

• 個人解法

IF OBJECT_ID('TC52','U') IS NOT NULL BEGIN
  DROP TABLE TC52
END

CREATE TABLE TC52(
  Employee_Name VARCHAR(20),
  Number INT,
  ManagerNumber INT,
  Wage MONEY
)

INSERT INTO TC52(Employee_Name, Number, ManagerNumber, wage)
SELECT 'Jacob', 345, NULL, 3000 UNION ALL
SELECT 'Marco', 873, NULL, 3000 UNION ALL
SELECT 'John', 844, 345, 1880 UNION ALL
SELECT 'James', 139, 844, 2010 UNION ALL
SELECT 'Ruth', 111, 873, 1550 UNION ALL
SELECT 'Margaret', 622, 345, 1300 UNION ALL
SELECT 'Mike', 999, 622, 2010 UNION ALL
SELECT 'Paul', 611, 139, 1400

;
WITH CTE AS
(
  SELECT Employee_Name , Number, ManagerNumber, wage , Employee_Name AS [Name]
  FROM TC52
  WHERE ManagerNumber IS NULL
  UNION ALL
  SELECT T2.Employee_Name, T2.Number, T2.ManagerNumber, T2.wage , T1.[Name]
  FROM CTE AS T1
    JOIN TC52 AS T2 ON T2.ManagerNumber = T1.Number
)
SELECT 
  T1.[Name] , 
  T1.[Total Wage] , 
  T1.[Max wage amount] ,
  ISNULL
    (
      STUFF
        (
          (
          SELECT ',' + Employee_Name
          FROM CTE T2
          WHERE T1.[Name] = T2.[Name] 
            AND T1.[Max wage amount] = T2.Wage
          ORDER BY Employee_Name
          FOR XML PATH('')
          ) 
          ,1,1,''
        )
    ,'') AS [Max wage employees]
FROM
  (
    SELECT 
      [Name] , 
      SUM(Wage) AS [Total Wage] , 
      MAX(CASE WHEN ManagerNumber IS NOT NULL THEN Wage ELSE 0 END) AS [Max wage amount]
    FROM CTE
    GROUP BY [Name]
  ) AS T1
ORDER BY T1.[Name]

• 個人解題邏輯

利用 CTE 來找出每一位職員最上位的 Leader 是哪一位

參考資料：
• TSQL Challenge 52 - Recursive Wage Calculation in a Parent-Child dimension