如果您对一个特定的日期感兴趣，这很简单。
看来您需要将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;
602392714
清零编程群