SQL Server – CTE Recursive SUM Value From Different Table

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.

  • Implementing IF Condition Within a T-SQL UPDATE Statement
  • How to write UPDATE SQL with Table alias in SQL Server 2008?
  • How to debug stored procedures in SQL server 2008 without CLR?
  • how to select columns as rows?
  • OLE DB provider “Search.CollatorDSO” returns “Command was not prepared”
  • SQL - safely downcast BIGINT to INT
  • 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 Recursive 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
    

    2 Solutions collect form web for “SQL Server – CTE Recursive SUM Value From Different Table”

    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 |
    +------------+---------------+------------+
    

    What you think about Nestet Sets patern (wiki)?

    OrgId   LeftIndex RightIndex    OrgName
    1       1         10            X COMPANY
    2       2         9             Administrator
    3       3         6             Adm 1
    4       7         8             Adm 2
    5       4         5             Adm 1_1
    
    SELECT O.OrgId, (SELECT Sum(EO.TotalHours)
       FROM CsOrganization CO
       INNER JOIN EmHisOrganization AS EHO ON CO.OrgId=EHO.OrgID
       INNER JOIN EmOvertime AS EO ON EHO.EmpId=EO.EmpId
       WHERE CO.LeftInde>=O.LeftIndex AND CO.RightIndex<=O.RightIndex) AS TotalHours
    FROM CsOrganization O
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.