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;
Comments
Post a Comment