Calculate Difference?

I am having trouble creating a sp for the following situation:

The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:

Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763

How can I calculate the actually miles traveled during the month in a query?

TIA,

RobTry something like:

select a.truck, a.reading - b.reading
from (select truck, reading from table where period = '2/28/03') b
inner join table a
on a.truck = b.truck
where a.period = '3/31/2003'|||Thank you for the quick response rnealejr, but this won't work for a table with 4 years of data. I am trying to do the following, but it is not quite right

select [Month] = t.period,
[Value] = t.reading - ISNULL(t2.reading, 0)
from mytable t
left join (select DATEADD(m,-1,period) X, reading from mytable) AS t2 ON t2.X = t.period

I think the answer is to create a udf that subtracts 1 from the day until the month changes, so I always get the last day of the month. Does this sound like the correct solution?

Rob|||select a.truck, a.period as enddate, a.milage - b.milage as milesrun
from test1 a join test1 b on a.truck = b.truck
where a.period = dateadd (mm, 1, b.period)
or b.period = dateadd (mm, -1, a.period)

I think you just missed the join on truckID. Also, since february 28 + 1 month is March 28, I decided to try it both ways (up and down). Hope this helps.|||OK - I finally got it. I ended up doing the following;

I first created a udf that looked like this

ALTER function PMonth(@.dt DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.ret DATETIME
SET @.ret = @.dt
WHILE DATEPART(m, @.dt) = DATEPART(m, @.ret)
SET @.ret = DATEADD(d,-1,@.ret)
RETURN @.ret
END

Then I wrote my sp like this:

SELECT [Truck] = m.truck,
[Period] = m.period,
[Value] = m.reading - ISNULL(m2.reading, 0)
FROM mytable m
LEFT JOIN (SELECT period, truck, reading FROM mytable) AS m2 ON m2.period = dbo.PMonth(m.period) AND m2.truck = m.truck

I am betting its not the most efficient solution (it takes 4 seconds), so if anyone has suggestions, please let me know.

Thanks,

Rob|||select CurrentMonth.Truck, CurrentMonth.Reading - isnull(PriorMonth.Reading, 0)
from YourTable CurrentMonth
left outer join YourTable PriorMonth
on CurrentMonth.Truck = PriorMonth.Truck
and convert(Char(7), CurrentMonth.Period, 120) = convert(Char(7), dateadd(m, 1, PriorMonth.Period), 120)

blindman|||Originally posted by blindman
select CurrentMonth.Truck, CurrentMonth.Reading - isnull(PriorMonth.Reading, 0)
from YourTable CurrentMonth
left outer join YourTable PriorMonth
on CurrentMonth.Truck = PriorMonth.Truck
and convert(Char(7), CurrentMonth.Period, 120) = convert(Char(7), dateadd(m, 1, PriorMonth.Period), 120)

blindman

Wow - this works much faster. Thank you very much blindman.

Rob|||Make sure you only have one entry per truck per month!