Stored Procedure Problem

[bbbarry]

First post here..

So here is the thing, I'm working on a school project which is a fleet-management system with GPS-devices.

No problems so far, the GPS etc is working like a charm.

I'm currently working on charts, to which i need to fill a xml file from a datatable.

The chart I want to make is a top 10 most/least consuming cars, expressed in liters/100km.

The data comes from a SQL Server database, in here lies the table
'Tankbeurten'

In this table, there is an ID('ID'), a vehicle ID ('VoertuigID'), a date on which the refueling happend (called 'TankDatum'), the amount of kilometers at the moment of refueling ('Kilometerstand'), and the amount of liters refueled ('AantalLiters')

so you've got
ID Vehicle ID - TankDatum - Kilometerstand - AantalLiters
1 5 10/03/2010 100000 50
2 5 11/03/2010 100400 50
...

the problem now, is that i need to build a stored procedure to get the top 10 most consuming cars, based on liters per 100 kilometers.

In the example above, the outcome should be (100400-100000)/50 = 8

how do you express this in 1 stored procedure so the outcome would become

vehicleID Consumption
5 8
17 7,7
... ...


Thanks in advance.

Edit: This software will be implied on an existing vehiclefleet. When the people using it add a new vehicle, they insert the current amount of kilometers the vehicle has.
This amount is kept in table 'Voertuigen' under the name 'BeginKilometers'. So when you add a vehicle that allready has 50.000 km's, and you add a new refueling-record to the table 'Tankbeurten', which contains 50 liters of fuel, it shouldn't say he did 50.000 kilometers with the 50 liters of fuel.

Seen that there will be many records in the 'Tankbeurten' table, it should be an average of all the records that imply per vehicle ID.

 

[rcombs4]

Your calculation and what fields you collect could cause an issue. You are implying that every time the car gets refueled the car's tank becomes full.

Say a car was on its way to city x. It was about to run out of fuel 10 KM outside of city x. So the driver puts in enough gas to make it to the town and will fill his tank when he makes it to city x.


The data would look something like this:
1 5 10/03/2010 100000 50
2 5 11/03/2010 100400 50
3 5 11/03/2010 100790 5
4 5 11/03/2010 100800 50

According to your calculation you would have...
(100790-100400)/5=78.


But as far as the SP..I can't think of any good solution for you.