#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Query - Retrieving Closest DateTime Records


    Hi, I'm new to the database world and beginning to dabble in SQL. I can't get my mind wrapped around the database mindset (I'm a PLC guy), and I'm having a little trouble with my query.

    I have two tables with the following columns (simplified):

    Login: TimeStmp, MessageText, UserID
    ChangeoverFloat: DateAndTime, Val, EventID

    I need to extract records showing the last person to login prior to certain changeover events occurring.

    I have the following code, but of course it returns a record for each login prior to the specified events.

    Code:
    SELECT b.DateAndTime, c.TimeStamp, c.UserID
    FROM CPUpgrade.dbo.Login As c INNER JOIN
    CPUpgrade.dbo.ChangeoverFloat AS b ON c.TimeStmp<=b.DateAndTime
    WHERE c.MessageText = 'Login' AND b.Val = 1
    Needless to say, this returns too many records for each event. I just need to find the single closest login records to each event that's taking place.

    Any help would be GREATLY appreciated!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Code:
    SELECT data.*
         , who.UserID
      FROM ( SELECT flt.EventID
                  , flt.DateAndTime
                  , MAX(log.TimeStmp) AS latest
               FROM CPUpgrade.dbo.ChangeoverFloat AS flt
             LEFT OUTER
               JOIN CPUpgrade.dbo.Login As log
                 ON log.TimeStmp <= flt.DateAndTime
                AND log.MessageText = 'Login' 
                AND flt.Val = 1
             GROUP
                 BY flt.EventID
                  , flt.DateAndTime  ) AS data
    LEFT OUTER
      JOIN CPUpgrade.dbo.Login AS who
        ON who.TimeStmp = data.latest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo