We run a 2nd shift that will sometimes produce after midnight. For daily reporting purposes, they’ve requested to group all transactions in the previous date.
I started with what I assumed is a fairly simple calculated field.
CASE
WHEN DATEPART(hour, LaborDtl.ClockInTime) <3
THEN DATEADD(day, -1, LaborDtl.ClockInDate)
ELSE LaborDtl.ClockInDate
END
But after the clock in eclipse the 0.15 mark, I’m getting the original date and not a back date. Feels like i’m missing something very simple
This is a slippery one! The DATEPART assumes you’re passing in a date time value, but the ClockInDate is just a decimal value from 0 to 24, representing the hour. So just drop off the datepart (hour, and you should be good to go!
Edit I hate to say it but I used chat GPT to figure this out…
There is a PayrollDate field on LaborDtl which should line up with the labor header’s payroll date; i.e., the afternoon 2nd shift started work.
All of our production reporting uses PayrollDate to make sure labor from the same shift is grouped together. ClockInDate and ClockOutDate are only used for supervisors to review and make sure nothing stands out when they do their end-of-shift audits.
Thank you both!
