27,579
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO @result
( UserID, ProjectID, CraftCode, CraftDescription, ConstructionTrade, EthnicityCode, Gender, WorkHours, EmployeeNumber, IsForman, FormanEmployeeID)
SELECT @pUserID, CW.ProjectID, CW.CraftCode, CW.CraftDescription,
CW.ConstructionTrade, CW.EthnicityCode, CW.Gender, Sum(CH.RHW+CH.OHW+CH.DHW)
AS Hours, Count(Distinct(CW.EmployeeID)) AS EmployeeNum, 1, FE.FormanEmployeeID
FROM @tblCPRWeekly CW INNER JOIN CPRHourly CH ON CW.CPRWeeklyID = CH.CPRWeeklyID
INNER JOIN FormanEmployee FE ON CW.EmployeeID = FE.EmployeeID
LEFT OUTER JOIN FormanEmployeePeriod FEP ON FE.FormanEmployeeID = FEP.FormanEmployeeID
WHERE CH.DateWorked Between @pStartDate AND @pEndDate and ch.ActiveRecord=1
AND CW.ProjectID = FE.ProjectID AND FE.ActiveRecord = 1
AND (((FEP.StartDate is null or FEP.StartDate = '1900-01-01 00:00:00.000') AND (FEP.EndDate is null or FEP.EndDate = '1900-01-01 00:00:00.000')) OR ((FEP.StartDate is null or FEP.StartDate = '1900-01-01 00:00:00.000') AND CH.DateWorked <= FEP.EndDate)
OR ((FEP.EndDate is null or FEP.EndDate = '1900-01-01 00:00:00.000') AND CH.DateWorked >= FEP.StartDate) OR (CH.DateWorked Between FEP.StartDate AND FEP.EndDate))
GROUP BY CW.ProjectID, CW.ConstructionTrade, CW.CraftCode, CW.CraftDescription, CW.EthnicityCode, CW.Gender, FE.FormanEmployeeID