Need to use minimum odometer reading from one month minus previous month in calculation

[neronikita]

Good morning!

I can't seem to wrap my head around this, as I don't get to create crystal reports every day. Working in Crystal 2008 with a SQL Server database.

Database Fields are RecID, FleetName, Truck, FullName, OccuredOn,Odometer,PositionLatitude, PositionLongitude, City, and State.

Example: I need to take the minimum odometer reading from February and subtract the minimum odometer reading from January to determine the exact miles in January. I need to repeat this to obtain miles for each month. If I use Minimum/Maximum Odometer Readings for each month, we lose miles between months. I know there has to be a fairly easy way to do this, but I just can't get anything to work. I need to do this for each truck individually.

OccuredOn is a datetime field. Odometer is a number field. Truck is a number field.

Thanks in advance for any suggestions you may have that I could try.

Have a great day!

Di

 

[HowardHammerman]

First step would be to group on the truck field. Then you need to filter your data to get the reading on first day of the month. Can we assume that there is always a reading on the first day of the month? Use Day({table.field}) = 1 in your query.
Next create a year and month field by using the totext function and extracting the six left characters. totext({table.datefield},"yyyyMM") will yield 20150704. note: case is important in the second argument. totext({table.datefield},"yyyyMM")[1 to 6] removes the last two characters. Sort on this formula. Now you and use the Previous function to get the prior month's reading.
Something like this: {table.field} - previous({table.field)

Sorry if this is a bit sketchy. It is July 4th




Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com