W
whitespaces
Guest
Hello there people, hope you can help with this problem..
I am developing a database system to look at another systems data and use this to create graphs etc. I work for a railway maintenance company and the data from this other database comes in the form of one table (with about 300,000 records).
The table has the folloing fields (briefly)..
Route, Track ID, FromMileage, ToMileage, ReadingDate, Reading
The Mileage comes in as a number in yards (which I convert into the format x.yyyy - where x is the miles and yyyy is the yards eg. 3.0880 is 3 and a half miles) - anyway this probably aint important. What I want to do is create a query that pulls out for each route and track ID the last 5 readings - and there equivalent reading data.
The data looks something like this..
Route TrackID FromMile ToMile ReadingDate Reading
CGJ2 1100 283.0880 283.0880 01/08/97 2.83
CGJ2 1100 283.0880 283.0880 01/08/98 1.29
CGJ2 1100 283.0880 283.0880 15/03/99 1.41
CGJ2 2100 283.0880 283.0880 01/08/97 2.83
CGJ2 2100 283.0880 283.0880 01/08/97 2.83
I want to display for each route, track ID and mileage the last 5 readings. Surely this should be pretty straight forward, but note that.
The readings are not taken generally taken the same time from one to another. (The frequencies are rarely fixed)
The dates and approximate frequencies will be different for each Track ID / Route.
Although generally the mileages are split into eigth mile sections - this isn't always the case.
Some Mileage sections have readings for approximately every month, others for approximately every 2 years etc. Some mileage sections have lots of reading dates going back years.
Sorry to drag this out massively, but I would appreciate any help. Cheers.
I am developing a database system to look at another systems data and use this to create graphs etc. I work for a railway maintenance company and the data from this other database comes in the form of one table (with about 300,000 records).
The table has the folloing fields (briefly)..
Route, Track ID, FromMileage, ToMileage, ReadingDate, Reading
The Mileage comes in as a number in yards (which I convert into the format x.yyyy - where x is the miles and yyyy is the yards eg. 3.0880 is 3 and a half miles) - anyway this probably aint important. What I want to do is create a query that pulls out for each route and track ID the last 5 readings - and there equivalent reading data.
The data looks something like this..
Route TrackID FromMile ToMile ReadingDate Reading
CGJ2 1100 283.0880 283.0880 01/08/97 2.83
CGJ2 1100 283.0880 283.0880 01/08/98 1.29
CGJ2 1100 283.0880 283.0880 15/03/99 1.41
CGJ2 2100 283.0880 283.0880 01/08/97 2.83
CGJ2 2100 283.0880 283.0880 01/08/97 2.83
I want to display for each route, track ID and mileage the last 5 readings. Surely this should be pretty straight forward, but note that.
The readings are not taken generally taken the same time from one to another. (The frequencies are rarely fixed)
The dates and approximate frequencies will be different for each Track ID / Route.
Although generally the mileages are split into eigth mile sections - this isn't always the case.
Some Mileage sections have readings for approximately every month, others for approximately every 2 years etc. Some mileage sections have lots of reading dates going back years.
Sorry to drag this out massively, but I would appreciate any help. Cheers.