Reynaldi Reynaldi - 7 months ago 17
SQL Question

SQL Server - CTE Recursive, Looping in Child's Data?

This question continues from the previous solved question (much thanks to Vladimir Baranov), which can be accessed here: SQL Server - CTE Recursive SUM Value From Different Table.

The data structures are pretty much the same as before, except there's another column named 'AttdDate' in EmOvertime to indicate employee's attendance date. Here's an example of data in 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 from CsOrganization and EmHisOrganization are the same as the previous question. Suppose I want to show all organization data with its TotalHours value on 12-May-2016, the query would be 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 result of the query is showing 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's data was looped as the quantity of its parent's data in EmOvertime, which has 4 same IDs. Hence, the value 12.00 is shown. How to fix this problem?

Any help would be greatly appreciated.

Answer

It is simple, if you are interested in one specific date.

It looks like you need to move the WHERE filter into the earlier part of the query. Into the CTE_OrgHours. CTE_OrgHours should return one row per organisation with the sum of the relevant hours. All filtering should happen in this query. Recursive part later expects to have one row per organisation in 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;