1. Registered User
Join Date
Sep 2003
Posts
30

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:

Code:
```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,

Rob

2. Registered User
Join Date
Feb 2002
Posts
2,232
Try something like:

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'

3. Registered User
Join Date
Sep 2003
Posts
30
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

Code:
```   select [Month] = t.period,
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

4. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
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.

5. Registered User
Join Date
Sep 2003
Posts
30
OK - I finally got it. I ended up doing the following;

I first created a udf that looked like this
Code:
```ALTER  function PMonth(@dt DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @ret DATETIME
SET @ret = @dt
WHILE DATEPART(m, @dt) = DATEPART(m, @ret)
RETURN @ret
END```
Then I wrote my sp like this:
Code:
```SELECT [Truck] = m.truck,
[Period] = m.period,
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

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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

7. Registered User
Join Date
Sep 2003
Posts
30
Originally posted by blindman
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

8. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595