Glowworm27
Programmer
I have a query that calculates vehicle mileage based on the miles driven fuel qty etc. Listing the driver and othe info.
Issue is the software that gathers the milage and fuel data has problems sometimes, and stops the pump before the vehicle is full. So the driver has to enter in new information to fuel the vehicle. What happens is when I go to calculate the mileage for that vehicle, the car was driven say 200 miles but only used .03 gal of fuel, which calculates a crazy 5000 mpg. The next record is for a milage of 2 miles with a qty of fuel about 13 gals. which calculates another crazy MPG this time in the opposite direction.
Problem is when aggregated with all the other users in the group it throws off the numbers.
from the looks of the data, I would like to sum up all the fuel and mileage numbers in the same day, and then calculate the mileage based on that aggregate. Problem is I cant get my group by function working properly.
Fuel Date is where i want the records summed on.
partial results below
for all the drivers I just want the daily numbers calculated in the MPG
So driver1 would have one record on 2010-05-28
driver1 20175 12.23 152 12.4285 2010-05-28
George Oakes
CEO & President
COPS Software, Inc.
Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!
Issue is the software that gathers the milage and fuel data has problems sometimes, and stops the pump before the vehicle is full. So the driver has to enter in new information to fuel the vehicle. What happens is when I go to calculate the mileage for that vehicle, the car was driven say 200 miles but only used .03 gal of fuel, which calculates a crazy 5000 mpg. The next record is for a milage of 2 miles with a qty of fuel about 13 gals. which calculates another crazy MPG this time in the opposite direction.
Problem is when aggregated with all the other users in the group it throws off the numbers.
from the looks of the data, I would like to sum up all the fuel and mileage numbers in the same day, and then calculate the mileage based on that aggregate. Problem is I cant get my group by function working properly.
Fuel Date is where i want the records summed on.
Code:
select
dbo.ProperCase(e.drivername) as [DriverName],
Max(m.meterreading) as meterreading,
Sum(f.fuelqty) as fuelqty,
Sum(ftc.milesdriven) as milesdriven ,
(Sum(ftc.milesdriven) / Sum(f.fuelqty)) as MPG,
e.groupid,
e.unitid as [Unit Id],
convert(datetime, substring(f.fueldate,1,8), 112) as [fuel date],
(convert(char(5), e.modelyear) + ' ' + left(dbo.ProperCase(e.equipmentmanufacturercode), 4) + ' ' + dbo.ProperCase(e.equipmentmodelcode)) as equip
from equipmentmaster e
inner join fueltransactions f
on f.systemequipmentnumber = e.systemequipmentnumber
inner join customermaster c
on c.customerid = e.registeredownercustomerid
join meterreading m
on m.referencerowid = f.rowid
inner join fueltransmpgcalc ftc on
f.systemequipmentnumber = ftc.systemequipmentnumber and f.fueldate = ftc.fueldate
inner join EquipmentStatusMaster esm on
esm.EquipmentStatusMasterUID = e.EquipmentStatusMasterUID
where Convert(DateTime, Left(f.fueldate,8), 112) between ('5/1/2010') and ('5/31/2010')
and (esm.EquipmentStatusMasterUID = 1 or esm.EquipmentStatusMasterUID = 4)
Group BY drivername, m.meterreading, f.fuelqty, ftc.milesdriven, e.groupid, e.unitid, f.fueldate
, (convert(char(5), e.modelyear) + ' ' + left(dbo.ProperCase(e.equipmentmanufacturercode), 4) + ' ' + dbo.ProperCase(e.equipmentmodelcode))
Code:
Name Meter fuelqty miles mpg fueldate
driver1 20023 12.50 191 15.28 2010-05-27
driver1 20173 0.03 150 5000.00 2010-05-28
driver1 20175 12.20 2 0.1639 2010-05-28
driver1 20345 12.89 170 13.1885 2010-05-29
driver2 40364 14.00 215 15.3571 2010-05-03
driver2 40564 15.20 200 13.1578 2010-05-12
driver2 40770 14.30 206 14.4055 2010-05-18
for all the drivers I just want the daily numbers calculated in the MPG
So driver1 would have one record on 2010-05-28
driver1 20175 12.23 152 12.4285 2010-05-28
George Oakes
CEO & President
COPS Software, Inc.
Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!