Cursor question

1 Oct 2005 12:05 AM

ninel

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

1 Oct 2005 7:27 AM

R.D

try this #PUNCHTABLE is a temp table, replace this with your actual table
---
CREATE PROCEDURE UpdateNullsFrompunch
AS
DECLARE @empid int ,@PunchOut varchar(10),@PunchIn varchar(10)
DECLARE PunchUpdate CURSOR
FOR SELECT empid,PunchOut,PunchIn FROM #PUNCHTABLE WHERE PUNCHOUT IS NULL
FOR UPDATE OF PunchOut
open PunchUpdate
FETCH NEXT FROM PunchUpdate INTO @empid,@PunchOut,@PunchIn
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
UPDATE #PUNCHTABLE SET PunchOut = (SELECT TOP 1 PunchIn FROM #PUNCHTABLE
WHERE PunchIn > @PunchIn and EmpId = @empid ORDER BY PunchIn)
WHERE EmpId = @empid AND PunchOut IS NULL AND PunchIn = @PunchIn
END
FETCH NEXT FROM PunchUpdate INTO @empid,@PunchOut,@PunchIn
END
CLOSE PunchUpdate
DEALLOCATE PunchUpdate

--
Regards
R.D
--Knowledge gets doubled when shared

Show quote

"ninel" 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 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
>
>

1 Oct 2005 11:49 AM

Hugo Kornelis

On Fri, 30 Sep 2005 19:05:21 -0500, ninel wrote:

(snip repeated question)

Hi Ninel,

I already answered this, about two hours before you reposted the
question. May I ask what the reason is for insisting on a slow,
non-portable, and non-scalable cursor/looping solution when you have
already been provided with a set-based answer?

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

1 Oct 2005 7:52 PM

--CELKO--

Your design is wrong. The nature of time is that it comes in
durations, not in points (known as Chronons in the literature). You
are using VIEWs to build the right design from a copy of a time card.

CREATE TABLE TimeCards
(emp_id CHAR(9) NOT NULL,
project_id CHAR(9) NOT NULL,
punchin_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
punchout_time DATETIME,
CEHCK (punchIn_time < punchout_time)
PRIMARY KEY (emp_id, punchIn_time));

Rows are not records and columns are not fields. One of the
differences is that a single data elelent can be put into more than one
column, as long as your have the right constraints to assure that they
represent that single data elelent. .