Tracking Employee Logtimes

  • 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

  • --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

  • 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

  • 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

  • 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