I have a table that contains employees punchin and punchout times.
An employee can punchin and out multiple times during day(Lunch/Breaks). Sometimes they forget to punch out, but punch in again. So I can have records that look like this:
EmpId Project PunchIn PunchOut
1 A 09:00:00 10:30:00
1 B 10:32:00 NULL
1 C 11:35:00 13:00:00
2 B 11:04:00 12:00:00
2 A 12:30:00 NULL
3 A 09:30:00 12:30:00
I need to loop through these records, figure out if the PunchOut is NULL and if it is, update the PunchOut with the next records PunchIn time. So EmpId 1 who worked on project B should have a PunchOut time of 11:35:00.
I can't figure out how to get the next records Punchin time to update the NULL value.
Can anyone please help me?
Thanks,
Ninel
An employee can punchin and out multiple times during day(Lunch/Breaks). Sometimes they forget to punch out, but punch in again. So I can have records that look like this:
EmpId Project PunchIn PunchOut
1 A 09:00:00 10:30:00
1 B 10:32:00 NULL
1 C 11:35:00 13:00:00
2 B 11:04:00 12:00:00
2 A 12:30:00 NULL
3 A 09:30:00 12:30:00
I need to loop through these records, figure out if the PunchOut is NULL and if it is, update the PunchOut with the next records PunchIn time. So EmpId 1 who worked on project B should have a PunchOut time of 11:35:00.
I can't figure out how to get the next records Punchin time to update the NULL value.
Can anyone please help me?
Thanks,
Ninel