BAQ summing rows issue

Epicor ERP 10
,
1

Hey there folks!

Well i ve tried for a while today to get some results but i feel i tried everything and gotten nowhere…

I need to return 2 rows for LaborEdit.
LaborHed has PayHours that seem to be 2 rows rolled up for AM PM. so for example, PayrollDate = 09/20/2024, EmpID = ABC, i should get 2 rows

09/20/2024______ABC_____5.00
09/20/2024______ABC_____3.00

thus providing total PayHours of 8.00

In the LaborDtl table are the clock in / out sides for the day. Also there are LaborHours that is the value between in/out. there can be any number per job so for example:

09/20/2024______ABC_____07.00____08.15_____1.15
09/20/2024______ABC_____08.16____10.25_____2.09
09/20/2024______ABC_____10.30____11.30_____1.00
09/20/2024______ABC_____11.40____12.01_____0.61

09/20/2024______ABC_____12.45____15.00_____2.55

AM LaborHours add up to 4.85
PM LaborHours add up to 2.55

The result i need is to sum the LaborHours for AM / PM and add them to the 2 return result rows like this

AM___09/20/2024______ABC_____5.00_____4.85
PM___09/20/2024______ABC_____3.00_____2.55

What im getting is results like 54 or 114. It seems to be summing up the summed values… I have tried a sub query on the LaborDtl table, summing on the TOP level table but each result is not right… I even got 50 rows even though there are only 5…

Will anyone be kind enough to guide me in the right direction to set this up?

The 3 tables in using are
LaborHed
LaborDtl (to get the individual job rows)
EmpBasic (to get the employee info)

DspClockInTime
DspClockOutTime
are the fields for the clock in / out time

I appreciate any help provided as always :slight_smile:

1 Like
2

Hey there! It felt easier to draw something up to explain my thoughts. But the biggest issue to me seems to be trying to sanitize the labor records to be a clean cut off at noon. So with that in mind, here is what I’m thinking

  1. Create two subqueries, one to return AM only records, and one for PM only records
  2. Create two subqueries that summarizes eaches details and does your rounding
  3. Finish with a union to “stack” the records on top of each other

image
image908×1046 49.9 KB

1 Like
3

Did you make any headway on this? happy to keep chatting about it if you still need help, or if my diagram wasn’t clear :sweat_smile:

4

Sorry bud, i thought i had replied already…

Had a redesign and was able to get the values to add up, in the end we didnt really AM or PM, just a list of entries for the whole day with a tally vs the pay hours

5

Done that before… the solution is eliminating the problem lol

2 Likes