Get next value for each of values from next table CTE

user_194076 0
Viewed 84

I have the following table: dbo.split 

Name  Time   
Alex  120
John  80 
John  300 
Mary  500
Bob   900

And then another table dbo.travel 

Name Time
Alex  150
Alex  160 
Alex  170
John  90
John  100
John  310  
Mary  550
Mary  600 
Mary  499 
Bob   800
Bob   700

For each value in table split I need to find the next value in table travel. I tried to do it with CTE a with ROW_NUMBER() to get next by group, but there's no way I can group by correct value, since dbo.split can containt multiple values for the same name.

I'm looking for the following output: 

Name  Time TravelTime 
Alex  120  150
John  80   90
John  300  310
Mary  500  550
Bob   900  NULL

Here's what I have so far but it fails because split table can have multiple records per person: 

;with result as (
 select t.*, 
 ROW_NUMBER() OVER (Partition BY t.Name order by t.Time) as rn 
 from travel t join split s 
 on t.Name = s.Name and t.TIME>s.Time
 )
sql-server-2014 sql
2 Answers

I would use apply:

select s.*, t.time
from split s outer apply
     (select top (1) t.*
      from travel t
      where t.name = s.name and t.time > s.time
      order by t.time asc
     ) t;

In this case, apply is doing essentially the same thing as a correlated subquery, so you could phrase it that way as well.

user_11440350

You can try as below

   Select * from(Select 
        Name,t.time,t1.time, 
       Row_number() over (partition by
        Name,t.time order by t1.time) rn
        from split t
       Join travel t1 on t.time <t1.time and 
          t.name =t1.name) 
          where 
           rn=1;
user_104974830
Related