Basic clock in / clock out data - how does it work

Epicor ERP 10
9

NOTE: You might not care about my collision stuff, I needed that for an API - but this is it in a Nutshell.

Supports DST, Timezone Diffs, Someone working past midnight, etc…

SELECT
    Erp.LaborHed.LaborHedSeq,
    Erp.LaborHed.EmployeeNum,
    Erp.EmpBasic.Name,
    Erp.LaborHed.ClockInDate, 
    Erp.LaborHed.ClockInTime,
    DATEADD(second, Erp.LaborHed.ClockInTime * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime)) AS ClockInDateTime,
    Erp.LaborHed.LunchOutTime,
                             
    DATEADD(SECOND, 
        1, -- Add 1 second to avoid ClockOut and Lunch Collisions
        DATEADD(second, Erp.LaborHed.LunchOutTime * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime)) 
        ) AS LunchOutDateTime,
 
    IIF(Erp.LaborHed.LunchOutTime > Erp.LaborHed.LunchInTime, Erp.LaborHed.LunchInTime + 24, Erp.LaborHed.LunchInTime) AS LunchInTime,
 
    DATEADD(SECOND,
        2, -- Add 2 seconds to avoid ClockOut and Lunch Collisions
        DATEADD(second, IIF(Erp.LaborHed.LunchOutTime > Erp.LaborHed.LunchInTime, Erp.LaborHed.LunchInTime + 24, Erp.LaborHed.LunchInTime) * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime))
    ) AS LunchInDateTime,
 
    IIF(Erp.LaborHed.ClockInTime > Erp.LaborHed.ClockOutTime, Erp.LaborHed.ClockOutTime + 24, Erp.LaborHed.ClockOutTime) AS ClockOutTime,
                             
    DATEADD(SECOND, 
        IIF((Erp.LaborHed.LunchInTime <> 0 and Erp.LaborHed.LunchOutTime <> 0) AND (Erp.LaborHed.LunchInTime <> Erp.LaborHed.LunchOutTime), 3, 1), -- Add 3 or 1 seconds to avoid ClockIn and Lunch Collisions
        DATEADD(second, IIF(Erp.LaborHed.ClockInTime > Erp.LaborHed.ClockOutTime, Erp.LaborHed.ClockOutTime + 24, Erp.LaborHed.ClockOutTime) * 3600, CAST(Erp.LaborHed.ClockInDate as DateTime))
    ) AS ClockOutDateTime,
 
    CASE WHEN (Erp.LaborHed.LunchInTime <> 0 and Erp.LaborHed.LunchOutTime <> 0) AND (Erp.LaborHed.LunchInTime <> Erp.LaborHed.LunchOutTime) THEN 1 ELSE 0 END AS Lunch,
    Erp.LaborHed.PayHours
 
    FROM Erp.LaborHed
        INNER JOIN Erp.EmpBasic ON Erp.EmpBasic.EmpID = Erp.LaborHed.EmployeeNum AND Erp.EmpBasic.Payroll = 1 AND Erp.EmpBasic.Company = Erp.LaborHed.Company
 
    WHERE
        Erp.LaborHed.ClockInTime <> 0.00
        AND Erp.LaborHed.ClockOutTime <>  0.00
        AND Erp.LaborHed.ClockInTime <> Erp.LaborHed.ClockOutTime
 
    ORDER BY
        ClockInDateTime desc, Erp.LaborHed.EmployeeNum