I am currently wrapping up something similar. As @kananga said If you look at the Required By Date, it may throw you off as schedulers could change it tomorrow.
I think the best way is to only consider a Job that has been started in your calculations, there are a few options:
- Look at JobHead.ProdQty vs JobHead.QtyCompleted
- Look at JobOper.LastLaborDate
- Look at JobOper.RunQty vs JobOper.QtyCompleted
- Join on LaborDtl.JobNum, LaborDtl.AssemblySeq, LaborDtl.OprSeq to get Calculated_HasAnyLabor
If you go the Qty routes, it won’t update the values until someone does Report Qty or End Activity.
If you want to actually figure out the EarnedHrs before any Qty is reported, you would have to do something like this BAQ - Time Conversion - #18 by JakeM (Calculate Realtime LaborHrs), once you have that and you have the JobOper.EstProdHours + JobOper.EstSetupHrs you can find the bottle necks, as well using JobOper.ProdStandard you can determine the Attained Standard, the trend etc, but only if they report qty otherwise you don’t really know how much they have completed, how much went to scrap etc…
TLDR; I would determine if a Job has been started and then consider the dates vs today’s date, or look at Operations Completed, or at Estimated Hours vs Realtime Labor Hours and you can determine with that information even if they are behind, when they will be done (BetterEstimatedCompletionDate). You can only get the trend if they report some qty.