27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT EmployeeID
,SUM(CASE WHEN IsHeadTime = 'HeadTime' THEN WorkHour END) AS Total_HeadTime
,SUM(CASE WHEN IsHeadTime = 'NOT HeadTime' THEN WorkHour END) AS Total_NOT_HeadTime
FROM (
SELECT A.EmployeeID
,B.WorkDate
,B.WorkHour
,CASE WHEN C.EmployeeID IS NOT NULL THEN 'HeadTime' ELSE 'NOT HeadTime' END AS IsHeadTime
FROM (Weekly A
INNER JOIN Hours B ON A.WeeklyID = B.WeeklyID)
LEFT JOIN EmployeeForman C ON B.WorkDate BETWEEN C.StartDate AND EndDate AND C.EmployeeID = A.EmployeeID
)X
GROUP BY EmployeeID