It's been a while, some help needed plz

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

It's been a while, some help needed plz

Author

Topic

jezeus
Starting Member

1 Post

Posted - 2007-08-21 : 17:43:07

Given the following tables:

Refuel
CarID OdometerReading LitersGas
1 1245 55.25
1 1457 16.96
1 1872 34.23
1 2193 25
1 2448 20.4
1 2508 5.52
1 2663 14.15
1 3075 41.82
1 3387 27.77
2 112145 36
2 112972 34.24
2 113357 30.03
2 113731 29.92
2 114130 36.7
2 114535 36.98
2 114943 41.41
3 18091 30.25
3 18291 16.6
3 18506 18.27
3 18791 23.37
3 19065 22.74
3 19364 28.40
3 19569 18.65

CAR
CarID Color Make Model
1 Blue Ford Taurus
2 Green VW Jetta
3 Blue Acura 1.7EL

Take it as a given that every time a car is refueled, the tank is filled.
To determine gas consumption (l/100km), take the LitersGas, divide it by kilometers traveled, and multiply by 100.
For example:
If the car takes 45 liters of gas, and had traveled 502 km since the previous time it was refueled, the fuel consumption would be: 45 liters / 502 km * 100 = 8.96 l/100km

1. Is it possible to make a single sql statement that would determine the fuel consumption and return the following table:

OdometerReading Consumption Color Make
1245 n/a Blue Ford
1457 7.54 Blue Ford
1872 8.19 Blue Ford
2193 7.78 Blue Ford
2448 7.84 Blue Ford
2508 8.33 Blue Ford
2663 9.03 Blue Ford
3075 9.95 Blue Ford
3387 8.65 Blue Ford
112145 n/a Green VW
112972 4.11 Green VW
113357 7.79 Green VW
113731 7.75 Green VW
114130 9.02 Green VW
114535 8.88 Green VW
114943 10.04 Green VW
18091 n/a Blue Acura
18291 8 Blue Acura
18506 8.37 Blue Acura
18791 8.07 Blue Acura
19065 8.02 Blue Acura
19364 9.36 Blue Acura
19569 8.78 Blue Acura

2. If you answered yes to question 1, please give the sql statement.
If you answered no, please explain why it isn’t possible, what extra information is needed and give a sample sql statement that should give the answer above.

-------------------

I got this so far

SELECT Refuel.OdometerReading, /* Big mess of code */ AS Consumption, CAR.Color, CAR. Make
FROM Refuel, CAR
WHERE Refuel.CarID = Car.CarID

I'm doubtful that this is the way to do it, but this is all I can remember... can someone help me or point me in the right direction please?

--

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 18:11:28

[code]SELECT q.OdometerReading,
q.LitersGas / (q.OdometerReading - q.PreviousOdometerReading) * 100.0 AS Consumption,
c.Color,
c.Make
FROM (
SELECT r.CarID,
r.LitersGas,
r.OdometerReading,
(SELECT MAX(e.OdometerReading) FROM Refuel AS e WHERE e.CarID = r.CarID AND e.OdometerReading < r.OdometerReading) AS PreviousOdometerReading
FROM Refuel AS r
) AS q
INNER JOIN Car AS c ON c.CarID = q.CarID[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources