Geek Answers Handbook

SQL Server - CTE recursive looping in child data?

This question continues compared to the previous question being solved (to a large extent thanks to Vladimir Baranov), which can be accessed: SQL Server - the recursive value of the CTE DBMS from another table .

The data structures are pretty much the same as before, with the exception of another column named "AttdDate" in EmOvertime to indicate the date the employee was visited. Here is an example of the data in the EmOvertime table .

Table Name : EmOvertime
EmpId    AttdDate     TotalOtReal    
2        2016-05-09   2.00          
2        2016-05-10   2.00        
2        2016-05-11   2.00       
2        2016-05-12   2.00        
3        2016-05-12   3.00

The data of CsOrganization and EmHisOrganization are consistent with the previous question. Suppose I want to show all the organization’s data with its TotalHours value on May 12, 2016, the query will look like this:

WITH
CTE_OrgHours
AS
(
SELECT
    Org.OrgId
    ,Org.OrgParentId
    ,Org.OrgName
    ,ISNULL(Overtime.TotalOtReal, 0) AS SumHours
    ,Overtime.AttdDate
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
    ,Overtime.TotalOtReal
    ,Overtime.AttdDate
)
,CTE_Recursive
AS
(
SELECT
     CTE_OrgHours.OrgId
    ,CTE_OrgHours.OrgParentId
    ,CTE_OrgHours.OrgName
    ,CTE_OrgHours.SumHours
    ,CTE_OrgHours.AttdDate
    ,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_OrgHours.AttdDate
    ,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
WHERE AttdDate = '2016-05-12'
GROUP BY
    StartOrgId
    ,StartOrgName
ORDER BY StartOrgId

But the query result looks like this:

OrgId      OrgName       TotalHours    
1          X COMPANY     14.00
2          Administrator 14.00
3          Adm 1         12.00 
4          Adm 2         0.00
5          Adm 1_1       0.00

While the correct one should be like this:

Desired Output
OrgId      OrgName       TotalHours    
1          X COMPANY     5.00
2          Administrator 5.00
3          Adm 1         3.00 
4          Adm 2         0.00
5          Adm 1_1       0.00

It seems that the child data was encoded as the amount of its parent data in EmOvertime , which has 4 identical identifiers. Therefore, the value 12.00 is shown. How to solve this problem?

Any help would be greatly appreciated.

+2
source share
1 answer

It is simple if you are interested in one particular date.

, WHERE . CTE_OrgHours. CTE_OrgHours . . CTE_OrgHours.

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
            AND Overtime.AttdDate = '2016-05-12'
    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;
+1

More articles:


All Articles