EpicorFree generatorPDF, Excel, Word exportEpicor KineticManufacturing

Epicor Labor Detail (LaborDtl) Hours by Employee SQL Query Generator

Plant HR and payroll teams often need a quick view of how many hours each employee booked in a week. This generator writes the SQL for Epicor's LaborDtl table, with an overtime flag and a drill-down by job operation.

Fill in

Company ID

Payroll date from

Payroll date to

Labor type

P is production labor, I is indirect labor in standard Epicor.

Overtime threshold hours

Generated code

Download:

Hours per employee with overtime flag

SELECT
  ld.EmployeeNum,
  SUM(ld.LaborHrs) AS TotalLaborHrs,
  SUM(ld.BurdenHrs) AS TotalBurdenHrs,
  COUNT(*) AS DetailLines,
  CASE WHEN SUM(ld.LaborHrs) > 40
       THEN SUM(ld.LaborHrs) - 40
       ELSE 0 END AS OvertimeHrs
FROM Erp.LaborDtl ld
WHERE ld.Company = 'EPIC06'
  AND ld.PayrollDate >= '2026-10-05'
  AND ld.PayrollDate <= '2026-10-11'
  AND ld.LaborType = 'P'
GROUP BY ld.EmployeeNum
ORDER BY TotalLaborHrs DESC;

Drill down: hours per employee per job operation

SELECT
  ld.EmployeeNum,
  ld.JobNum,
  ld.AssemblySeq,
  ld.OprSeq,
  ld.PayrollDate,
  SUM(ld.LaborHrs) AS LaborHrs
FROM Erp.LaborDtl ld
WHERE ld.Company = 'EPIC06'
  AND ld.PayrollDate >= '2026-10-05'
  AND ld.PayrollDate <= '2026-10-11'
  AND ld.LaborType = 'P'
GROUP BY ld.EmployeeNum, ld.JobNum, ld.AssemblySeq, ld.OprSeq, ld.PayrollDate
ORDER BY ld.EmployeeNum, ld.PayrollDate;

Column names reflect the common Erp.LaborDtl layout in Epicor 10 and Kinetic, but confirm them in your database or Data Dictionary Viewer before use. Run read-only against a reporting copy where possible. The threshold applies to the whole date range, so use a single payroll week for a true weekly overtime check, and apply your own local overtime rules.

Labor hours questions answered from Epicor

Optional: see how plain-language questions over Epicor labor data can replace one-off SQL requests.

Optional. The tool and every export work without this. No spam.

How to use the query

Enter your company ID, the payroll date range and the overtime threshold, then copy the first query into a BAQ with a custom SQL expression, SQL Server Management Studio or your reporting tool. Use one payroll week at a time so the threshold means weekly overtime. The second block shows which jobs and operations drive the hours, which helps supervisors explain overtime on a particular order.

What LaborDtl contains

Erp.LaborDtl stores one row per labor transaction, linked to its header in LaborHed. Useful columns include EmployeeNum, JobNum, AssemblySeq, OprSeq, LaborType, LaborHrs, BurdenHrs and PayrollDate. LaborHrs is the time reported by the employee, while BurdenHrs is the time used for burden absorption, and the two can differ when a multi-resource or multi-employee operation applies. For payroll hours, use the payroll date, not the clock-in date, since shifts crossing midnight can split.

LaborHrs is employee hours reported
BurdenHrs drives burden absorption
PayrollDate groups shifts that cross midnight
Approval status should be checked before payroll export

Reading the result

Employees at the top of the list with overtime above zero need review before payroll closes. Compare the total against clock hours if you use a separate time clock, and check for open labor entries that have not been ended, which understate hours. A large difference between production and indirect hours is also worth a look, since it shows time not charged to jobs.

Common mistakes

Do not run a multi-week range and read the overtime column as weekly overtime. Avoid joining to the employee table without the company filter, which multiplies rows in multi-company databases. Remember that unapproved labor may still appear in the table, and that corrections may be posted as new rows with later dates.

Frequently Asked Questions

Which table has Epicor labor hours?

Labor transactions are stored in Erp.LaborDtl, with a header record in Erp.LaborHed. Hours are in LaborHrs, and burden hours in BurdenHrs. Payroll reporting usually relies on PayrollDate on the detail line, and approval flags in the table decide what can be moved to payroll.

Can I use this inside a BAQ?

Yes, the SQL can be adapted in a BAQ with a subquery or a calculated field, or run directly in SQL Server. In a BAQ you would normally use the Query Builder tables and filters instead of raw text, but the column names and grouping stay the same.

Why do my hours not match the time clock?

Common reasons are open labor entries without an end time, unapproved entries, rounding rules in clock-in processing, and shifts crossing midnight that were grouped by clock-in date. Compare on PayrollDate and check the labor header status for the employee.

Is the overtime flag a legal overtime calculation?

No. It is a simple hours-above-threshold check. Real overtime depends on local law, collective agreements, daily limits, holidays and which hours count as worked. Use it to find employees to review, then apply your payroll rules.

If you want to ask questions like which employees exceeded 45 hours last week without writing SQL, ERPray can answer them read-only over your Epicor data, showing the query it ran.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Epicor tools

See all

Back to All Tools