Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007

    Question Unanswered: Difference Between Rows/Cols

    I've got a table that includes, among other things, 2 columns of timestamps as datetimes. I'm trying to calculate the difference between the 2 columns on different rows. For instance, say I have the following:
    CREATE TABLE LogStats (ID int, Login datetime, Logout datetime)
    INSERT INTO LogStats VALUES (123,'12/19/2007 5:58:34 AM','12/19/2007 5:59:08 AM')
    INSERT INTO LogStats VALUES (123,'12/19/2007 6:00:02 AM','12/19/2007 7:01:59 AM')
    INSERT INTO LogStats VALUES (123,'12/19/2007 7:14:09 AM','12/19/2007 8:13:11 AM')
    INSERT INTO LogStats VALUES (123,'12/19/2007 8:20:23 AM','12/19/2007 9:29:42 AM')
    I am wanting to display the login/logout times along with the total amount of time logged in, which is just the datediff between Login and Logout, and the total amount of time logged out, which is the datediff between the current Login and the latest Logout that is less than Login. So I want something like this:
    SELECT ID, Login, Logout,
    FORMAT(DATEADD('s', DATEDIFF('s',Login,Logout), '0:00:00'),"hh:mm:ss") as TimeLoggedIn,
    FORMAT(DATEADD('s', DATEDIFF('s',PrevLogout,Login), '0:00:00'),"hh:mm:ss") as TimeLoggedOut
    FROM LogStats
    ORDER BY Login
    Obviously PrevLogout is not defined here. So how do I make PrevLogout equal the latest Logout that is less than Login?

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    It's going to have to involve a DMAX("Logout","LogStats", Criteria) with criteria that is ensuring that the returned result isn't later than the current login.

    I won't try to get too specific here, I'll only embarrass myself... too much "Christmas cheer" ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2007
    Nevermind. In this particular case I can do some post-processing on the data before presenting it. I'll calculate it there and just get the basic info via Access.
    Last edited by VentureFree; 12-27-07 at 02:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts