Join Most Recent Records - 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 2008 Forums

Transact-SQL (2008)

Join Most Recent Records

Author

Topic

bmturney
Starting Member

7 Posts

Posted - 2012-03-08 : 17:42:35

I have an issue that I think should be possible, but I haven't been able to figure out an efficient way to do it via SQL.

I have two tables... each table contains data keyed by a datetime field, but the datetime keys aren't exact matches for the correlating records in each file. I want to join the records in table1 with the records in table2 so that for each record in table1 it gets matched up with the most recent correlating record from table2 where the collection time for the record in table2 does not exceed the collection time for the record in table1 (confusing, right?)

Here's an example:
Table 1
Time1 ObjID1 Data1 Data2 ObjID2
13:40:45 1 aaa bbb 101
13:42:32 2 ccc ddd 102
13:43:18 1 eee fff 101
13:45:56 1 ggg hhh 101
13:48:43 2 iii jjj 102

Table 2
Time2 ObjID2 Data3 Data4
13:39:19 101 zzz yyy
13:42:28 102 xxx www
13:45:31 101 vvv uuu
13:46:34 101 ttt sss
13:48:56 102 rrr qqq
13:50:43 101 ppp ooo

So the resulting records will look like this

Time1 ObjID1 Data1 Data2 ObjID2 Time2 Data3 Data4
13:40:45 1 aaa bbb 101 13:39:19 zzz yyy
13:42:32 2 ccc ddd 102 13:42:28 xxx www
13:43:18 1 eee fff 101 13:39:19 zzz yyy
13:45:56 1 ggg hhh 101 13:45:31 vvv uuu
13:48:43 2 iii jjj 102 13:42:28 xxx www

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 17:57:23

[code]
SELECT t1.Time1, t1.ObjID1,t1.Data1,t1.Data2,t1.ObjID2,t2.Time2,t2.Data3,t2.Data4
FROM table1 t1
CROSS APPLY (SELECT TOP 1 Time2,Data3,Data4
FROM table2
WHERE ObjID2 = t1.ObjID2
AND Time2 < Time1
ORDER BY Time2 DESC) t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bmturney
Starting Member

7 Posts

Posted - 2012-03-09 : 08:31:24

Thanks!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:49:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources