I found some good logic on this site which calculates clock in vs clock out times. It even included over midnight scenarios where an employee punches out the next day. The code below handles this:
sum(case when TimeType='IN' then -1 * (datepart(hh,DayWorked) + datepart(mi,DayWorked) /60.0)
else datepart(hh,DayWorked) + datepart(mi,DayWorked)/60.0 end) TotMTCHrs, crew
from #TEMP_NEW_TIMESHEET, Employee EE
Where #TEMP_NEW_TIMESHEET.EmployeeId = EE.Id
group by EmployeeId, convert(varchar(10),DayWorked,101),Firstname, Lastname, crew ) t
along with: cast(Round(case when TotMTCHrs < 0 then 24 + TotMTCHrs else TotMTCHrs end,2) as decimal(16,2)) TotMTCHrs
Which accounts for over midnight.
Now a new situation has entered the picture.
I need to be sure that there is always a clock in and clock out pair. If not then the lone Clock In or Clock Out entry should be ignored.
I am not sure how to incorporate this.
Person clocks In at 9:00pm and clocks out at 4:00 am the next day. The query will write two entries: the first is the time between 9:00pm and Midnight and the second between Midnight and 4:00am.
This works great!
Person clocks in at 8:00am then clocks out at 5:00pm- it creates a single record showing 9 hours. This works great!
An employee Clocks Out at 4:00am, but there was no matching Clock in from that day or the day before, it writes a record from Midnight to 4:00am (4 hours), when it should ignore this clock out because there is no matching Clock In.
Pairs should be configured by an employeeId and a CrewId. So in Example 3 for this particular employee there was no clock In under this CrewId. So no transaction should have been written from Midnight to 4:00am.
Can you help!? Thanks for your time.