I've got a SQL table that contains train agendas. The table looks something similar to this:
My real database contain several tables, however for this just the one above is pertinent. Different trainNumber might have different quantity of legs. With different departure station selected with a user, I wish to output all approaching routes from that station.
The output must contain Departure some time and Arrival station. But I'd rather not range from the legs among. Can anybody guide me within the right direction how I'm able to accomplish this? I attempted utilizing a max statement. But did not quite get results generate an income desired to.
Also, there might be multiple departures through the same train number on the day that.
You should make use of the combination (DepartureTime + TrainNumber) because the answer to your query, get the most arrival time considering that mixture of values, after which discover exactly what the corresponding ArrivalStation is. So you could do this an inner join between your Schedule along with a arranged version of itself, i.e.
SELECT TrainTableA.TrainNumber ,TrainTableA.DepartureTime ,ArrivalStation FROM (SELECT /* all upcoming train routes for given station */ TrainNumber ,DepartureTime ,ArrivalTime ,ArrivalStation FROM Schedule WHERE DepartureStation = givenStation ) as TrainTableA INNER JOIN (SELECT /* Just the last station for each departure */ TrainNumber ,DepartureTime ,Max(ArrivalTime) as a FROM Schedule GROUP BY TrainNumber ,DepartureTime ) as TrainTableB ON TrainTableA.TrainNumber = TrainTableB.TrainNumber AND TrainTableA.DepartureTime = TrainTableB.DepartureTime AND TrainTableA.ArrivalTime = TrainTableB.a
I can not quite tell in the question for those who have a universal indicator from the route sequence, and so i used max(ArrivalTime). You might use max(LegID) if each LegID is more than the main one before it. Also, I believed the ArrivalTime includes the date, so 1:00 AM on the following day continues to be after 10:00 PM on the day that. So, obviously, adapt to taste.