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.

Solution 1:

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;