Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Diablos
Starting Member
10 Posts |
 Posted - 2011-03-31 : 13:07:45
|
| the info in my databse is like this Example(the are thousands of lines like this but with different circuits etc):Tipo Info Schedule Departure_Hour Arrival_Hour CircuitSchedule Day H000136 07:50:00 10:00 1CHP13Schedule Day H000136 23:00:00 23:40:00 1CHP13Schedule Day H000218 23:40:00 00:20:00 1CHP13Schedule Day H000490 23:05:00 23:40:00 1CHP13Schedule Next_Day H000219 23:50:00 01:40:00 1CHP13Schedule Next_Day H000219 02:50:00 03:40:00 1CHP13I want the query to return the first departure hour (07:50, this is done), and the last arrival hour(01:40) but i'm having trouble finding the last arrival hour. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-31 : 14:16:30
|
| Why isn't 02:50:00 the minimum hour? What are you grouping on?JimEveryday I learn something that somebody else already knew |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-01 : 07:39:25
|
| I'm guessing the problem is arrival hours the following day? So you want the last arrival for a departure on that day - so the last row is excluded as it departs the following day.select circuit, min(departure_hour), max(arrival_hour)from (select circuit, daparture_hour, arrival_hour = case when arrival_hour < departure_hour then arrival_hour+1 else arrival_hour end) agroup by circuit==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Query to find the latest arrival hour