January 6, 2006 at 2:06 pm
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
January 7, 2006 at 11:15 am
--Script:
SET NOCOUNT ON
--Declare the table variable
DECLARE @LogTimes TABLE(
EmployeeId varchar(10) NULL,
Project VarCHAR(10) NULL ,
LogIn DATETIME NULL,
LogOut DATETIME NULL,
Time int NULL)
--Insert the records
INSERT @LogTimes SELECT '123', 'A', CONVERT(VARCHAR(10), GETDATE(), 120) + ' ' + CONVERT(VARCHAR(8), '08:00:00', 108), NULL, 20
INSERT @LogTimes SELECT '123', 'B', CONVERT(VARCHAR(10), GETDATE(), 120) + ' ' + CONVERT(VARCHAR(8), '09:00:00', 108), CONVERT(VARCHAR(10), GETDATE(), 120) + ' ' + CONVERT(VARCHAR(8), '09:35:00', 108), 35
INSERT @LogTimes SELECT '246', 'A', CONVERT(VARCHAR(10), GETDATE(), 120) + ' ' + CONVERT(VARCHAR(8), '09:30:00', 108), NULL, 20
Print 'Records BEFORE the update'
SELECT * from @logtimes
UPDATE @LOGTIMES
SET LOGOUT = DATEADD (mi, time, login)
WHERE EMPLOYEEID IN (SELECT EMPLOYEEID FROM @LOGTIMES GROUP BY EMPLOYEEID HAVING COUNT(1) > 1)
AND LOGOUT IS NULL
Print 'Records AFTER the update'
SELECT * from @logtimes
/*************
OUTPUT
**************/
Records BEFORE the update
EmployeeId Project LogIn LogOut Time
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ -----------
123 A 2006-01-07 08:00:00.000 NULL 20
123 B 2006-01-07 09:00:00.000 2006-01-07 09:35:00.000 35
246 A 2006-01-07 09:30:00.000 NULL 20
Records AFTER the update
EmployeeId Project LogIn LogOut Time
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ -----------
123 A 2006-01-07 08:00:00.000 2006-01-07 08:20:00.000 20
123 B 2006-01-07 09:00:00.000 2006-01-07 09:35:00.000 35
246 A 2006-01-07 09:30:00.000 NULL 20
January 9, 2006 at 7:58 am
Thank you so much for your help. It seems I forgot to mention something else. The logout time of the max record for employee 123 must be left NULL. This is a tracking system of telemarketing employee phone calls. The rep may still be on the phone with a customer when this file gets imported so we don't want to update the last record for an employee. We want to update their previous records.
Is there a way to do this?
Thank you so much,
Ninel
January 9, 2006 at 8:10 am
I came up with this. It works, but I'm sure it's not even close to being a good way of doing this.
[Code]
UPDATE #LOGTIMES
WHERE EMPLOYEEID IN (SELECT Max(EMPLOYEEID) FROM #LOGTIMES GROUP BY EMPLOYEEID HAVING COUNT(1) > 1)
AND ilog In (SELECT max(ilog) FROM #LOGTIMES GROUP BY EMPLOYEEID HAVING COUNT(1) > 1)
AND LOGOUT IS NULL
[/code]
iLog is an identity column I added.
Is there a better way of writing this query?
Thanks,
Ninel
January 9, 2006 at 8:45 am
I found this query which works, but it updates all records that have a time.
UPDATE A
SET logout =
(SELECT TOP 1 logout =
CASE
WHEN b.logout IS NOT NULL THEN convert(char(8), convert(datetime, a.login) + a.time, 108)
END
FROM LogTimes B
WHERE B.employeeid=A.employeeid
AND B.date = A.date
AND B.login > A.login
ORDER BY login
)
FROM LogTimes A
WHERE (
RTRIM(LTRIM(Time)) ''
AND Time '00:00:00'
AND ISNULL(Time, 'x') 'x'
)
AND ( ISNULL(logout,'x') = 'x' OR rtrim(ltrim(Logout)) ='')
How can I modify it to update only records that have more than one record per day AND if an employee has more than one record update all the records except for the last one.
Thanks,
Ninel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply