JOIN based on MIN DATEDIFF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

JOIN based on MIN DATEDIFF

Author

Topic

nick45
Starting Member

1 Post

Posted - 2005-12-06 : 10:51:40

I have searched the forums and haven't found exactly what I need. Here is my problem:

Table Event (ID,EventID,PersonID,... , Date)
Table GPS (ID,PersonID, x, y,... , Date)

For Table Event, say I have eventID=1, personID=1 was logged at Date='12/05/2005 10:45:33'

Table GPS randomly records where PersonID is on 12/05/2005 at these times:
10:44:21
10:44:54
10:45:21
10:45:56
10:46:20

I would like to JOIN the GPS record that is closest in time to the time the event occured (10:45:33) and return x, y. The record I would need to join on is the GPS record logged at 10:45:21.

How would I go about doing this?

Thanks!
Nick.

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-06 : 13:15:49

[code]
USE Northwind
GO

SET NOCOUNT ON

CREATE Table Event ([ID] int,EventID int, PersonID int, [Date] datetime)
CREATE Table GPS ([ID] int, PersonID int, x char(1), y Char(1), [Date] datetime)
GO

INSERT INTO Event([ID], EventID, PersonID, [Date])
SELECT 1, 1, 1, '12/05/2005 10:45:33'

INSERT INTO GPS([ID], PersonID, x, y, [Date])
SELECT 1, 1, 'x', 'y', '10:44:21' UNION ALL
SELECT 1, 1, 'x', 'y', '10:44:54' UNION ALL
SELECT 1, 1, 'x', 'y', '10:45:21' UNION ALL
SELECT 1, 1, 'x', 'y', '10:45:56' UNION ALL
SELECT 1, 1, 'x', 'y', '10:46:20'
GO

SELECT ABS(DATEDIFF(ss,CONVERT(varchar(8),e.[Date],108),g.[Date])) , *
FROM Event e
JOIN GPS g
ON e.PersonID = g.PersonID
WHERE ABS(DATEDIFF(ss,CONVERT(varchar(8),e.[Date],108),g.[Date])) = (
SELECT MIN(ABS(DATEDIFF(ss,CONVERT(varchar(8),e2.[Date],108),g2.[Date])))
FROM Event e2
JOIN GPS g2
ON e2.PersonID = g2.PersonID
WHERE g2.PersonID = g.PersonID
GROUP BY e2.PersonID)
GO

SET NOCOUNT ON
DROP TABLE Event, GPS
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources