Cursor question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor question

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-09-30 : 17:21:56

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-30 : 17:51:36

This should do it:


select
	a.EmpId,
	a.PunchIn,
	PunchOut = min(b.PunchIn)
from
	mytable a
	join
	mytable b
	on 	a.EmpId = b.EmpId and
		a.PunchIn < b.PunchIn and
		a.PunchOut is null
group by
	a.EmpId,
	a.PunchIn

CODO ERGO SUM

mdgryn
Starting Member

6 Posts

Posted - 2005-10-01 : 03:37:06

quote:

Originally posted by Michael Valentine Jones

This should do it:


select
	a.EmpId,
	a.PunchIn,
	PunchOut = min(b.PunchIn)
from
	mytable a
	join
	mytable b
	on 	a.EmpId = b.EmpId and
		a.PunchIn < b.PunchIn and
		a.PunchOut is null
group by
	a.EmpId,
	a.PunchIn

CODO ERGO SUM

mdgryn
Starting Member

6 Posts

Posted - 2005-10-01 : 03:45:40

update
mytable set PunchOut = b.PunchIn
from mytable
inner join (select min(PunchIn) as PunchIn,EmpId , Project from mytable aS b where b.EmpId =mytable.EmpId and b.Project =mytable.Project and b.PunchIn > mytable.PunchIn ) as b
on mytable.PunchOut is null

ninel
Posting Yak Master

141 Posts

Posted - 2005-10-03 : 12:16:08

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?

[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')
[code]

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

Thanks,
Ninel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources