September 28th, 2012, 01:32 PM
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.
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.
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
Any help would be GREATLY appreciated!
September 28th, 2012, 03:10 PM
FROM ( SELECT flt.EventID
, MAX(log.TimeStmp) AS latest
FROM CPUpgrade.dbo.ChangeoverFloat AS flt
JOIN CPUpgrade.dbo.Login As log
ON log.TimeStmp <= flt.DateAndTime
AND log.MessageText = 'Login'
AND flt.Val = 1
, flt.DateAndTime ) AS data
JOIN CPUpgrade.dbo.Login AS who
ON who.TimeStmp = data.latest