数据结构与以前几乎相同，不同之处在于 EmOvertime中 还有另一个名为“ AttdDate”的 列， 用于指示员工的出勤日期。这是EmOvertime 表中数据的示例。

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

来自 CsOrganization 和 EmHisOrganization
的数据与上一个问题相同。假设我想在2016年5月12日显示具有其TotalHours值的所有组织数据，查询将是这样的：

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

但是查询的结果显示如下：

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

虽然正确的应该是这样的：

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

似乎在 EmOvertime 中将孩子的数据作为其父数据的数量进行了 循环，它具有4个相同的ID。因此，将显示值12.00。如何解决这个问题？

任何帮助将不胜感激。