Reynaldi Reynaldi - 6 months ago 15
SQL Question

SQL - CTE Recursive SUM Value

I have a table with data stored hierarchically in a table named CsOrganization like in the following table.

Table Name : CsOrganization
OrgId OrgParentId OrgName
1 NULL X COMPANY
2 1 Administrator
3 2 Adm 1
4 2 Adm 2
5 3 Adm 1_1


And then there's a table named EmHisOrganization which related to CsOrganization table like in the following table.

Table Name : EmHisOrganization
EmpId OrgId
1 2
2 2
3 3
4 4
5 5


Each employee will have overtime data based on the organization they have, and they stored in EmOvertime table.

Table Name : EmOvertime
EmpId TotalOtReal
1 1.00
2 2.00
3 3.00
4 2.00
5 1.00


The problem is I need to get the sum of TotalHours based on each Organization. The sum of TotalHours must also sum all of its child's TotalHours data. So far, I managed to figure out their parent and child, but I can't figure out how to get TotalHours data from a different table and make a sum of it. As far as I know, I need to join those tables to get TotalHours, but unfortunately CTE doesn't allow to use OUTER JOIN in the syntax. Here's the output I want based on the examples above:

Desired Output
OrgId OrgName TotalHours
1 X COMPANY 9.00
2 Administrator 9.00
3 Adm 1 4.00
4 Adm 2 2.00
5 Adm 1_1 1.00


Notice that TotalHours of Adm 1 is from the sum Employee with ID 3 which has a value of 3.00 in TotalHours column and Employee with ID 5 which has a value of 1.00 in TotalHours column, which resulted 4.00 in the desired table. The same goes when OrgId with ID 1 and 2 got the value of 9.00 in TotalHours.

Any help would be greatly appreciated.

Edited On 09/05/2016, 12.02, added relationship of the tables and my query attempts.

Here's the look of the relationship: Table Relationship.

My Query attempts (these resulted 0.00 on each Organization, but the anchor query is showing the right value, if the where clause is deprecated):

With OrgTree (OrgId, OrgName, TotalHours) AS
(
SELECT orgId, orgN, SUM(eReal) AS TotalHours
FROM (SELECT OrgId AS orgId, OrgName AS orgN, CASE WHEN x.TotalOtReal IS NULL THEN 0 ELSE x.TotalOtReal END AS eReal
FROM (SELECT f.OrgId, f.OrgName, o.TotalOtReal
FROM dbo.CsOrganization AS f LEFT OUTER JOIN
(SELECT OrgId, SUM(TotalOtReal) AS TotalOtReal
FROM (SELECT a.EmpId, a.OrgId, b.TotalOtReal
FROM dbo.EmHisOrganization AS a LEFT OUTER JOIN
(SELECT EmpId, SUM(TotalOtReal) AS TotalOtReal
FROM dbo.EmOvertime AS a
GROUP BY EmpId) AS b ON a.EmpId = b.EmpId) AS a_1
GROUP BY OrgId) AS o ON f.OrgId = o.OrgId
) AS x WHERE OrgId = 1) AS xx
GROUP BY orgId, orgN

UNION ALL
SELECT a.OrgId, a.OrgName, TotalHours FROM dbo.CsOrganization a
INNER JOIN OrgTree o ON a.OrgParentId = o.OrgId
)
SELECT a.OrgId, a.OrgName, SUM(a.TotalHours) AS TotalHours FROM OrgTree a
GROUP BY a.OrgId, a.OrgName

Answer

Sample data

DECLARE @CsOrganization TABLE (OrgId int, OrgParentId int, OrgName nvarchar(50));
INSERT INTO @CsOrganization (OrgId, OrgParentId, OrgName) VALUES
(1, NULL, 'X COMPANY'),
(2, 1   , 'Administrator'),
(3, 2   , 'Adm 1'),
(4, 2   , 'Adm 2'),
(5, 3   , 'Adm 1_1');

DECLARE @EmHisOrganization TABLE (EmpId int, OrgId int);
INSERT INTO @EmHisOrganization (EmpId, OrgId) VALUES
(1, 2),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

DECLARE @EmOvertime TABLE (EmpId int, TotalOtReal float);
INSERT INTO @EmOvertime (EmpId, TotalOtReal) VALUES
(1, 1.00),
(2, 2.00),
(3, 3.00),
(4, 2.00),
(5, 1.00);

Query

  • CTE_OrgHours calculates the simple sum of overtime hours for all employees of each organisation.
  • CTE_Recursive is the recursive CTE that traverses the hierarchy of organisations.
  • Final SELECT groups the traversed tree to sum hours for each node (organisation) of the tree.

Run this query step-by-step, CTE-by-CTE and examine intermediate results to get a better understanding of how it works.

WITH
CTE_OrgHours
AS
(
    SELECT
        Org.OrgId
        ,Org.OrgParentId
        ,Org.OrgName
        ,ISNULL(SUM(Overtime.TotalOtReal), 0) AS SumHours
    FROM
        @CsOrganization AS Org
        LEFT JOIN @EmHisOrganization AS Emp ON Emp.OrgId = Org.OrgID
        LEFT JOIN @EmOvertime AS Overtime ON Overtime.EmpId = Emp.EmpId
    GROUP BY
        Org.OrgId
        ,Org.OrgParentId
        ,Org.OrgName
)
,CTE_Recursive
AS
(
    SELECT
         CTE_OrgHours.OrgId
        ,CTE_OrgHours.OrgParentId
        ,CTE_OrgHours.OrgName
        ,CTE_OrgHours.SumHours
        ,1 AS Lvl
        ,CTE_OrgHours.OrgId AS StartOrgId
        ,CTE_OrgHours.OrgName AS StartOrgName
    FROM CTE_OrgHours

    UNION ALL

    SELECT
         CTE_OrgHours.OrgId
        ,CTE_OrgHours.OrgParentId
        ,CTE_OrgHours.OrgName
        ,CTE_OrgHours.SumHours
        ,CTE_Recursive.Lvl + 1 AS Lvl
        ,CTE_Recursive.StartOrgId
        ,CTE_Recursive.StartOrgName
    FROM
        CTE_OrgHours
        INNER JOIN CTE_Recursive ON CTE_Recursive.OrgId = CTE_OrgHours.OrgParentId
)
SELECT
    StartOrgId
    ,StartOrgName
    ,SUM(SumHours) AS TotalHours
FROM CTE_Recursive
GROUP BY
    StartOrgId
    ,StartOrgName
ORDER BY StartOrgId;

Result

+------------+---------------+------------+
| StartOrgId | StartOrgName  | TotalHours |
+------------+---------------+------------+
|          1 | X COMPANY     |          9 |
|          2 | Administrator |          9 |
|          3 | Adm 1         |          4 |
|          4 | Adm 2         |          2 |
|          5 | Adm 1_1       |          1 |
+------------+---------------+------------+