sql - Compare row pair from one table with a row from another table -

sql - Compare row pair from one table with a row from another table -

i have 2 tables: table 1

column column b time r t 11/3/14 8:30:00 t 11/3/14 8:35:00

table 2

column column b time2 s t 11/3/14 8:30:00 u t 11/3/14 8:32:00

i have write sql query checks each consecutive row pair in table 1, if there row in table 2 falls in between. in case, 2nd row table 2 occurs @ 8:32 falls in between first 2 rows table 1. have been struggling since morning, there many answers show how compare consecutive rows of same table, not sure how can compare them 2nd table.

desired output 2nds row of table 2: u t 11/3/14 8:32:00

can help?

if using sql server, dataset:

declare @table1 table (a varchar(10), b varchar(10), time1 datetime) declare @table2 table (a varchar(10), b varchar(10), time2 datetime) insert @table1 values ('r', 't', '11/3/14 8:30:00'), ('a', 't', '11/3/14 8:32:00'), ('r', 't', '11/3/14 8:40:00'), ('a', 't', '11/3/14 8:42:00') insert @table2 values ('s', 't', '11/3/14 8:30:00'), ('u', 't', '11/3/14 8:32:00')

you can obtain table can compare @table2 using cross apply:

select t1.time1 r_time, t.time1 a_time @table1 t1 cross apply ( select top 1 t2.time1 @table1 t2 = 'a' , t2.time1 >= t1.time1 ) t = 'r'

the above gives output:

r_time a_time ----------------------------------------------- 2014-11-03 08:30:00.000 2014-11-03 08:32:00.000 2014-11-03 08:40:00.000 2014-11-03 08:42:00.000

thus, final query looks this:

select * @table2 t3 cross apply ( select t1.time1 r_time, t.time1 a_time @table1 t1 cross apply ( select top 1 t2.time1 @table1 t2 = 'a' , t2.time1 >= t1.time1 ) t = 'r' ) t4 t3.time2 between t4.r_time , t4.a_time

output:

a b time2 r_time a_time ------------------------------------------------------------------------------- s t 2014-11-03 08:30:00.000 2014-11-03 08:30:00.000 2014-11-03 08:32:00.000 u t 2014-11-03 08:32:00.000 2014-11-03 08:30:00.000 2014-11-03 08:32:00.000

sql database