Cursor question

[ninelgorb]

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

 

[gmmastros]

You don't need a cursor for this. You just need to get a little creative.

I've run out of time, and this is NOT complete. But it may help you get started.

Declare @Temp Table(EmpId Integer, Project VarChar(10), PunchIn SmallDateTime, Punchout SmallDateTime)

Insert Into @Temp Values(1,'A','09:00:00','10:30:00')
Insert Into @Temp Values(1,'B','10:32:00',NULL)
Insert Into @Temp Values(1,'C','11:35:00','13:00:00')
Insert Into @Temp Values(2,'B','11:04:00','12:00:00')
Insert Into @Temp Values(2,'A','12:30:00',NULL)
Insert Into @Temp Values(3,'A','09:30:00','12:30:00')

Select * from @Temp

Update T1
Set     T1.Punchout = T2.PunchIn
From 	@Temp T1
		Inner Join @Temp T2 On T1.EmpId = T2.EmpId
			And T1.Punchout Is NULL
			And T2.PunchIn > T1.PunchIn

Select * from @Temp

It's not complete yet, because it can produce the wrong results.

Add this to the code...
Insert Into @Temp Values(1,'C','14:35:00','15:00:00')

and you'll notice that it is using the wrong value. I have to go now, so I can't spend anymore time working on this. Sorry for the incomplete answer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Evadman]

Modified your code.

Declare @Temp Table(EmpId Integer, Project VarChar(10), PunchIn SmallDateTime, Punchout SmallDateTime)

Insert Into @Temp Values(1,'A','09:00:00','10:30:00')
Insert Into @Temp Values(1,'B','10:32:00',NULL)
Insert Into @Temp Values(1,'C','11:35:00','13:00:00')
Insert Into @Temp Values(1,'C','14:35:00','15:00:00')
Insert Into @Temp Values(2,'B','11:04:00','12:00:00')
Insert Into @Temp Values(2,'A','12:30:00',NULL)
Insert Into @Temp Values(3,'A','09:30:00','12:30:00')

Declare @MissedPunch Table(EmpId Integer, PunchIn SmallDateTime)
	
Insert into @MissedPunch(EmpID, PunchIn)
Select t1.EmpID, min(t1.PunchIn) From @Temp T1
        Inner Join @Temp T2 On T1.EmpId = T2.EmpId 
		WHERE not T1.Punchout Is NULL
        	And T2.PunchIn < T1.PunchIn
		group by t1.empid

select * from @MissedPunch

Select * from @Temp

Update T2
Set     T2.Punchout = T1.PunchIn
From    @MissedPunch T1
        Inner Join @Temp T2 On T1.EmpId = T2.EmpId 
	WHERE T2.Punchout Is NULL

		

Select * from @Temp

 

[vongrunt]

update A
set PunchOut = 
(	select top 1 PunchIn 
	from myTable B
	where B.EmpID=A.EmpID and B.PunchIn > A.PunchIn
	order by PunchIn
)
from myTable A
where A.PunchOut is null

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ninelgorb]

Thank you all for helping me out. I really appreciate it.

I'd like to add something else...
How can I update the PunchOut with the PunchIn time, but minus a second?

Declare @Temp Table(EmpId Integer, Project VarChar(10), PunchIn SmallDateTime, Punchout SmallDateTime)

Insert Into @Temp Values(1,'A','09:00:00','10:30:00')
Insert Into @Temp Values(1,'B','10:32:00',NULL)
Insert Into @Temp Values(1,'C','11:35:00','13:00:00')
Insert Into @Temp Values(1,'C','14:35:00','15:00:00')
Insert Into @Temp Values(2,'B','11:04:00','12:00:00')
Insert Into @Temp Values(2,'A','12:30:00',NULL)
Insert Into @Temp Values(3,'A','09:30:00','12:30:00')

I would like to update Emp1 PunchOut time to "11:34:59".

Thanks,
Ninel

 

[Evadman]

Use the Dateadd function, and change the update to update -1 second.

Declare @Temp Table(EmpId Integer, Project VarChar(10), PunchIn SmallDateTime, Punchout SmallDateTime)

Insert Into @Temp Values(1,'A','09:00:00','10:30:00')
Insert Into @Temp Values(1,'B','10:32:00',NULL)
Insert Into @Temp Values(1,'C','11:35:00','13:00:00')
Insert Into @Temp Values(1,'C','14:35:00','15:00:00')
Insert Into @Temp Values(2,'B','11:04:00','12:00:00')
Insert Into @Temp Values(2,'A','12:30:00',NULL)
Insert Into @Temp Values(3,'A','09:30:00','12:30:00')

Declare @MissedPunch Table(EmpId Integer, PunchIn SmallDateTime)
    
Insert into @MissedPunch(EmpID, PunchIn)
Select t1.EmpID, min(t1.PunchIn) From @Temp T1
        Inner Join @Temp T2 On T1.EmpId = T2.EmpId 
        WHERE not T1.Punchout Is NULL
            And T2.PunchIn < T1.PunchIn
        group by t1.empid

select * from @MissedPunch

Select * from @Temp

Update T2
Set     T2.Punchout = dateadd(ss, -1, T1.PunchIn)
From    @MissedPunch T1
        Inner Join @Temp T2 On T1.EmpId = T2.EmpId 
    WHERE T2.Punchout Is NULL

        

Select * from @Temp

there is a small issue with this code now that I think about it, but I don't have time to test it. I think that it will leave a punchout field as null if there are >1 that are null on the same day. However I believe runnning again will update the second null field.