Cursor question

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 record
s
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
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1On Fri, 30 Sep 2005 21:21:15 GMT, ninel g via webservertalk.com wrote:

>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 recor
ds
>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 an
d
>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
Hi Ninel,
No need to use a cursor for this!
UPDATE PunchTable
SET PunchOut = (SELECT MIN(a.PunchIn)
FROM PunchTable AS a
WHERE a.PunchIn > PunchTable.PunchIn
AND a.EmpId = PunchTable.EmpId)
WHERE PunchOut IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)