sql - Get current country from trip information -

i have simple table (trip), stores trip information following

trip_id traveler    country_from    country_to  departure_date  arrival_date 1        test1       germany         italy       2016-01-01     2016-01-02 1        test1       italy           france      2016-04-01     2016-04-02 1        test1       france          italy       2016-08-28     2016-08-28 1        test1       italy           germany     2016-08-30     2016-10-31  2        test2       france          usa         2016-01-28     2016-02-28  2        test2       usa             france      2016-08-30     2016-10-31  

actually means

test1 travels: germany -> italy -> france -> italy -> germany  test2 travels: france -> usa -> france 

departure_date , arrival_date defines when traveler leave country_from, , when in country_to... time spend in flight

(i agree, table awful, , have lots of normalizing issues cant manage it, have have)

i need write query, return traveler name, , country, in traveler located (or if many countries first country) given time period...

for example time period 2016-03-01 - 2016-04-02 query should return

test1 italy test2 usa 

please advice optimal way result, have tried join table few variations, come corner cases not covered

you need beginning , end of trip each country in 1 row. in sql server 2012+ can use lead() purpose. rest of query handling overlapping timeperiods:

select t.* (select t.country_to country, t.arrival_date,              lead(t.departure_date) on (partition t.traveler order arrival_date) departure_date       trips t       t.traveler = @traveler      ) t @date1 <= t.departure_date ,       @date2 >= t.arrival_date; 

edit:

you can same thing in earlier versions using outer apply:

select t.* (select t.country_to country, t.arrival_date,              t2.departure_date       trips t outer apply            (select top 1 t2.*             trips t2             t2.traveler = t.traveler ,                   t2.arrival_date > t.arrival_date             order t2.arrival_date            ) t2       t.traveler = @traveler      ) t @date1 <= t.departure_date ,       @date2 >= t.arrival_date;