Daily Burden Hours: Actual vs Calculated

BurdenHoursPerDay

--Sums up burden hours on each machine as recorded in MES, calculates what the hours should be based on qty made and Production Rate.

SELECT L.PayrollDate, Sum(L.BurdenHrs) AS [Total Burden Hours], COUNT(L.LaborDtlSeq) AS [#Activities], COUNT(DISTINCT L.ResourceID) AS [Total Active Machines],  
Sum(L.LaborQty / (J.ProdStandard + .0001)) AS [Total Calculated Burden Hours], UPPER(L.ResourceID)
FROM ERP10PROD.Erp.LaborDtl L
LEFT JOIN ERP10PROD.Erp.JobOper J ON
L.Company = J.Company AND
L.AssemblySeq = J.AssemblySeq AND
L.JobNum = J.JobNum AND
L.Oprseq = J.OprSeq
WHERE L.ResourceID <> '' AND J.ProdStandard > 2 AND (((L.ResourceGrpID='LATHCNC1') AND (L.LaborType='P')) OR 
((L.ResourceGrpID='LATHCNC2') AND (L.LaborType='P')) OR 
((L.ResourceGrpID='MILLCNC1') AND (L.LaborType='P')) OR 
((L.ResourceGrpID='MILLCNC2') AND (L.LaborType='P')) OR 
((L.ResourceGrpID='SWISSCNC') AND (L.LaborType='P')) OR 
((L.ResourceGrpID='SCREWLG') AND (L.LaborType='P')) OR 
((L.ResourceGrpID='SCREWSM') AND (L.LaborType='P')))
GROUP BY L.PayrollDate, L.ResourceID
ORDER BY L.PayrollDate, L.ResourceID DESC