Need help adding Table Criteria

Epicor ERP 10
1

I have been asked to create a BAQ that calculates average labor hours by job and part over a date range.

I need to add something, that in SQL would be Labor.Dtl.ClockOutTime-Labor.Dtl.ClockinTime>0
How can I accomplish this?

Here is my where clause in SQL; I need to know where to add the italicized line to the BAQ

where
erp.labordtl.expensecode=‘direct’
and erp.labordtl.clockouttime-erp.labordtl.clockintime>0
and erp.labordtl.LaborEntryMethod<>‘q’
and erp.jobhead.jobcomplete=1
and erp.jobhead.JobCompletionDate>‘01/01/22’ and erp.jobhead.jobcompletiondate<‘04/01/22’

image
image1063×682 46.6 KB

2

Why not just use LaborDtl.LaborHrs>0

1 Like
3

I see what you are trying to get at, but subtracting the times may not be the best option. What if someone starts a job at 11:00pm, them stops the job at 4:00am? I think this would break your BAQ. What about using LaborHrs in the LaborDtl table?

4

If you REALLY want to do it this way, create a calculated field that = clockout-clockin, then set a subquery criteria on that calculated field.

5

Well I feel like an idiot, the LaborHrs field, I totally missed. Thanks all.

1 Like