help with group by

[Glowworm27]

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.

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))

partial results below

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.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Glowworm27]

never mind I figured it out....

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
inner 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, e.groupid, e.unitid, Convert(DateTime, Left(f.fueldate,8), 112)
, (convert(char(5), e.modelyear) + ' ' + left(dbo.ProperCase(e.equipmentmanufacturercode), 4) + ' ' + dbo.ProperCase(e.equipmentmodelcode))



George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net