Calculate value based on previous record 1

[rookietech25]

I have a table where we record fuel purchases and when a truck crosses a state line.

I need to calculate how many miles each truck traveled in the states it passed through.





Key Truck_Name Odometer Date State Gallons Dollars

Auto# Text Number Date Text Number Currency

1 L01 100 12/1/06 LA

2 L01 200 12/1/06 MS

3 L01 300 1201/06 MS 20 $49.00

4 L02 2983 12/1/06 LA

5 L01 500 12/02/06 TN

6 L02 3300 12/2/06 AR

7 L02 3800 12/2/06 LA

8 L01 800 12/2/06 AR

9 L01 1000 12/2/06 LA





The results I would like to see would look like.



Truck_Name State Total_Miles Fuel_Total Dollar_Total

Text Text Number Number Currency

L01 LA 100

L01 MS 300 20 $49.00

L01 TN 300

L01 AR 200

L02 LA 317

L02 AR 500



I have Tries a couple of queries like the one below but can't seem to get the criteria correct.



SELECT A.IFTA_ID,B.[What State],A.[What State],A.[Truck Number], A.Date, (A.Odometer - B.Odometer) As [Difference]



From tbliftalog A INNER JOIN tbliftalog B

ON A.[Truck Number] = B.[Truck Number] AND A.Date > B.Date



WHERE B.Date =

(Select MAX(Date) From tbliftalog X

Where X.Date < A.Date

AND X.[Truck Number] = A.[Truck Number]);

 

[lespaul]

not sure I'm getting your logic. Here's the example data you gave, I have bolded all the records dealing with truck L01.
[tt]
Key Truck_Name Odometer Date State Gallons Dollars
1 L01 100 12/1/06 LA
2 L01 200 12/1/06 MS
3 L01 300 12/1/06 MS 20 $49.00
4 L02 2983 12/1/06 LA
5 L01 500 12/2/06 TN
6 L02 3300 12/2/06 AR
7 L02 3800 12/2/06 LA
8 L01 800 12/2/06 AR
9 L01 1000 12/2/06 LA[/tt]

Truck L01 started with an odometer reading of 100 in LA. The truck then has another reading of 200 in MS. The truck has travelled 100 miles. Is that the 100 reported in Total_Miles in the first record below? How do you get a Total_Miles of 300 in the second record below? According to the data above, there were two readings in MS, one for 200 and one for 300???
Why would Total_Miles in TN be 300? If the odometer was 300 in MS and then 500 in TN, wouldn't that be 200 miles? Why don't you want the final record above to be included? The truck goes an additional 200 miles from AR to LA?
[tt]
Truck_Name State Total_Miles Fuel_Total Dollar_Total
L01 LA 100
L01 MS 300 20 $49.00
L01 TN 300
L01 AR 200
L02 LA 317
L02 AR 500[/tt]


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[rookietech25]

Thanks Leslie for your reply!!! Yes I want the final record to be included. I want the the table to show the final miles calculated for each state entered.

 

[PHV]

Perhaps this ?
SELECT [Truck Number], [What State], Sum(Difference) AS Total_Miles
FROM (
SELECT A.[Truck Number], A.Odometer, A.[What State], Min(B.Odometer)-A.Odometer AS Difference
FROM tbliftalog A INNER JOIN tbliftalog B ON A.[Truck Number] = B.[Truck Number]
WHERE A.Odometer < B.Odometer
GROUP BY A.[Truck Number], A.Odometer, A.[What State]
) AS C
GROUP BY [Truck Number], [What State]
ORDER BY [Truck Number], Min(Odometer)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rookietech25]

Thanks alot that was awesome!!!!How can I go about getting the total gallons and dollars for each state. Once again Thanks!!!

 

[PHV]

SELECT [Truck Number], [What State], Sum(Difference) AS Total_Miles[!], Sum(Gallons) AS Fuel_Total, Sum(Dollars) AS Dollar_Total[/!]
FROM (
SELECT A.[Truck Number], A.Odometer, A.[What State], Min(B.Odometer)-A.Odometer AS Difference[!], A.Gallons, A.Dollars[/!]
FROM tbliftalog A INNER JOIN tbliftalog B ON A.[Truck Number] = B.[Truck Number]
WHERE A.Odometer < B.Odometer
GROUP BY A.[Truck Number], A.Odometer, A.[What State][!], A.Gallons, A.Dollars[/!]
) AS C
GROUP BY [Truck Number], [What State]
ORDER BY [Truck Number], Min(Odometer)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rookietech25]

Great!!! Thanks Alot!!!!

 

[rookietech25]

I'm having trouble with getting the [Enter Start Date:] And [Enter End Date:]on the field [Date]in tbliftalog