Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: 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:

    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. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Try 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'

  3. #3
    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,
              [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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #5
    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)
             SET @ret = DATEADD(d,-1,@ret)
          RETURN @ret
       END
    Then I wrote my sp like this:
    Code:
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  7. #7
    Join Date
    Sep 2003
    Posts
    30
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure you only have one entry per truck per month!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •