I am trying to create a BAQ that will list Pay Hours vs Labor Hours by department on a weekly basis. I will be breaking this down further to include Direct and Indirect Hours I am able to calculate the Pay Hours by itself without an issue. I can also calculate the Labor Hours itself without an issue. When joining the LaborDtl table to LaborHed table, grouped by week, my Pay Hours increase significantly. ie, should be 2686.18 hours becomes 5100.23 when joining the two tables.
I’ve tried counting the distinct values for LaborHedSeq and LaborDtlSeq then creating a Calculated Field as follows: sum(LaborHed.PayHours)/CountLabDtl*CountLabHed. PayHours ends up at 2706.24. LaborHrs remains accurate at 2581.85.
Below is my query so far. I haven’t included departments at this point. Hoping someone can steer me in the right direction. Appreciate your input. I’m running Epicor ERP 10.1.400.
select
(datepart(WEEK,Labordtl.PayrollDate)) as [Calculated_Week],
(sum(Laborhed.PayHours)) as [Calculated_SumPayHrs],
(sum(LaborDtl.LaborHrs)) as [Calculated_SumLabHrs],
(Count(distinct(Laborhed.LaborHedSeq))) as [Calculated_CountLabHed],
(count(distinct(Labordtl.LaborDtlSeq))) as [Calculated_CountLabDtl],
(count(distinct(labordtl.LaborHedSeq))) as [Calculated_CountLabHedDtl],
(sum(LaborHed.PayHours)/CountLabDtl*CountLabHed) as [Calculated_TotalPayHOurs]
from Erp.LaborHed as LaborHed
inner join Erp.LaborDtl as LaborDtl on
LaborHed.Company = LaborDtl.Company
And
LaborHed.LaborHedSeq = LaborDtl.LaborHedSeq
group by (datepart(WEEK,Labordtl.PayrollDate))
Not an expert in SQL but i believe the “Pay Hrs” are not actual hrs but estimated pulled in from shift maintenance. Ex. if employees as scheduled to work 8 hrs per day, then the “Pay Hours” would pull in as 8 hrs but the actual hrs (labordtl.laborhrs) could be different than the normal “scheduled” hrs (overtime, or not working the 8 hr shift) for each employee.
LaborDtl.LaborHrs = actual hrs reported
LaborHed.PayHours = hrs calculated based on normal shift hrs : Job managment > Setup > Shift. (Shift hrs - Lunch hrs = Pay Hours)
I’m pretty sure LaborHed.PayHours is the actual hours employee will be paid for, linked to Time and Expense Entry > Time > Daily Time > Summary > Detail.
Maybe we have different setups. I just tested it out and my payhours come from Shift Maintenance. Now, i do manually change the pay Hours to match “Total Labor” hrs once i am done entering all the labor hrs for each employee to make sure both totals match. The “Pay Hours” could be over ridden where as “Total labor” hrs come from labor details and can only be changed by updating the labor entry details.
The Pay Hour (LaborHed.PayHours) are the difference between the Clock In
time and the Clock Out time, for the Labor Header (the amount of hours they
are paid for). The Total Labor are the sum of any detail records in the
Labor Header (LaborDtl.LaborHrs).
If you change the shift field it will automatically pull in the clock in/clock out times. But if you use MES for actual times, the pay hours will come from actual time worked (ClockOutTime - ClockInTime) - Lunch (if applicable)
Danny,
My BAQ is a bit crude, but this should get you steered in the right direction. I had to do a left join on LaborHed subquery to the LaborDtl subquery because we have some employees/departments that don’t ever clock to jobs.
Follow up: you may need to put in date filters or also pull and group by datepart(year, LaborHed.PayrollDate) otherwise it will combine week 1 2017 with week 1 2016, 2015, etc.
We actually have the same situation going on. This worked perfectly. Thanks a ton for your help. I am new to SQL/BAQ’s so this will help me understand where I was going wrong.
