I currently have a table that has a list of locations a schedule will use and I want to be able to get the location code and platform for both the current row and the next one, and then have the database return a distinct pair.
Sometimes the Platform is null.
HeaderId LocationOrdinal TIPLOC Platform 1 0 GRAINEW 1 1 1 GRAIN A 1 2 CLFFD12 1 3 HOOJ 2 1 4 GRVSEND 5 1 5 SPHEADJ A 1 6 DARTFD 1 7 DARTFDJ 1 8 CRFDSPB 1 9 CRFD 1 1 10 SIDCUP 1 11 LEEE 1 12 HTHRGRN 2 0 HULL 7 2 1 HESSLE 1 2 ...
I currently use the following SQL to get the pairs of Tiplocs (without platforms)
select distinct TIPLOC as A, next_location as B from (select st.*, lead(TIPLOC) over (partition by HeaderId order by LocationOrdinal) as next_location from sched_timings st ) st where next_location is not null;
A B GRAINEW GRAIN GRAIN CLFFD12 CLFFD12 HOOJ HOOJ GRVSEND GRVSEND SPHEADJ SPHEADJ DARTFD DARTFD DARTFDJ DARTFDJ CRFDSPB CRFDSPB CRFD CRFD SIDCUP
A APlat B BPlat GRAINEW 1 GRAIN A GRAIN A CLFFD12 CLFFD12 HOOJ 2 ...
UPDATE: I have done the code below which seems to work but not sure if there's a more efficient approach.
select distinct A, AP, next_location as B, next_location_plat as BP from (select st.TIPLOC as A, st.Platform as AP, lead(TIPLOC) over (partition by HeaderId order by LocationOrdinal) as next_location, lead(Platform) over (partition by HeaderId order by LocationOrdinal) as next_location_plat from sched_timings st ) st where next_location is not null;