none
Help with query

    Question

  • Hi,

    I have a table which has columns that records the logon and logoff times of the users every thime they do so. here are the column descriptions

    MSG DATE - DATE of the Logon\logoff

    MSG TIME - TIME of the logon\log off

    Log Type -  Type of activity ( Logon or Logoff.)

    User NAme

    Q)My requirement is to find who has logged on in a period of time and last login time when supplied with username Can you please help?

    Many Thanks,

    Bhanu

    Tuesday, August 05, 2014 12:09 AM

Answers

  • Lets start with preparing test table @T for your requirement. If you want to execute it for your table, just replace the table name from @T with your table name.

    DECLARE @T TABLE( MsgDate Date, MsgTime TIME, LogType VARCHAR(20), Username VARCHAR(50)) INSERT INTO @T SELECT '2014-08-01', '20:17:02', 'logon', 'A' UNION ALL SELECT '2014-08-01', '21:17:02', 'logoff', 'A' UNION ALL SELECT '2014-08-02', '10:17:02', 'logon', 'A' UNION ALL SELECT '2014-08-02', '20:17:02', 'logoff', 'A' UNION ALL SELECT '2014-08-04', '10:17:02', 'logon', 'A' --Replace table name from @T to your table name and execute below script in your environment:

    --Who has logged on in a period of time SELECT * FROM @T WHERE CAST(MsgDate AS DATETIME) + CAST(MsgTime AS DATETIME) BETWEEN '2014-08-01 20:17:02' AND '2014-08-01 21:17:02' AND LogType = 'Logon' --last login time when supplied with username SELECT CAST(MAX(CAST(MsgDate AS DATETIME) + CAST(MsgTime AS DATETIME)) AS DATE) LastMsgDate, CAST(MAX(CAST(MsgDate AS DATETIME) + CAST(MsgTime AS DATETIME)) AS TIME) LAstMsgTime FROM @T WHERE Username = 'A'



    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by bhanu_nz Tuesday, August 05, 2014 1:44 AM
    Tuesday, August 05, 2014 12:33 AM