Tracking Employees LogTimes - 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)

Tracking Employees LogTimes

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-01-06 : 16:02:53

I have a table that tracks employees log times throughout the day for different projects.


CREATE TABLE LogTimes( 
EmployeeId varchar(10) NULL, 
Project  VarCHAR(10) NULL , 
LogIn varchar(10) NULL,
LogOut  varchar(10) NULL,
Time  varchar(10) NULL)

INSERT LogTimes SELECT '123', 'A', '08:00:00',NULL, '00:20:00'
INSERT LogTimes SELECT '123', 'B', '09:00:00','09:35:00', '00:35:00'
INSERT LogTimes SELECT '246', 'A', '09:30:00', NULL, '00:25:00'

A file is imported every 30 minutes and the table gets updated. If the record doesn't exist it gets inserted into the table. If the record exists only the logOut and time fields get updated.

Here is my question...

I need to update the logout field using a calculation of the login + calltime to get the logout time. I have to do this ONLY if there is more than one record for that employee. If there is only one record for an employee and their logOut time is NULL I don't need to update it.

So the logout field for employee 123's frst record should be updated to '08:20:00' (Login: 08:00:00 + time: 00:20:00 = 08:20:00), but employee 246 logout field must remain null.

Does anyone know how to write this query?

Thanks,
Ninel

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-06 : 16:09:26

You can use: select EmployeeId, count(*) from logtimes group by EmployeeId to get a count of how many rows per user, then use those results to know who to update. You could modify with having count(*) > 1 to get just those who have more than 1.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-01-06 : 16:29:31

[code]
update L set L.LogOut = convert( varchar(10), (cast(LogIn as datetime) + cast(Time as datetime)), 8)
from dbo.LogTimes L
where L.LogOut is null
and exists (select 1 from dbo.LogTimes X
where X.EmployeeID = L.EmployeeID
group by X.EmployeeId
having COUNT(*) > 1)
[/code]

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources