CODEWITHSUNDEEP
sql-server-2008-r2
user7076259
user7076259

Reputation:

I need to sum the mileage ran each month per vehicle

I have a series of vehicles that are fueled every day, entering the total fuel and the odometer reading. I need to sum the mileage ran each month per vehicle. My table is fuel, vehicle is bus, date is service_date, odometer reading is mileage, fuel entered is quantity.

So I would take the mileage from the first of every month and subtract that from the first of the previous month per vehicle, in this case 3237. To make matters worse, if the vehicle was not fueled on the first, I would want the mileage from the last day it was fueled on the previous month.

service_date    bus mileage quantity    
7/1/2018    202 149654  34  
7/3/2018    202 150256  40.5    
7/4/2018    202 150562  42  
7/6/2018    202 150853  41  
7/7/2018    202 151191  37  
7/8/2018    202 151323  23.6    
7/15/2018   202 151502  39  
7/13/2018   202 151806  45  
8/1/2018    202 152891  37  3237

Upvotes: 1

Views: 195

Answers (1)

Jesse Potter
Jesse Potter

Reputation: 847

Not sure I understand all of your specific requirements, but here is how I would write a query to report on mileage for a fleet of buses.

First I would get the ending mileage for each month using GROUP BY and MAX like this:

SELECT bus_id
    , MAX(bus_mileage) AS 'ending_mileage'
    , DATEPART(YEAR, service_date) AS 'year'
    , DATEPART(MONTH, service_date) AS 'month'
    , MAX(service_date) AS 'last_service_date'
FROM @mileage
GROUP BY bus_id
    , DATEPART(YEAR, service_date)
    , DATEPART(MONTH, service_date)

Then I would use that as a subquery to pull the previous month's ending mileage. This would give you the starting mileage for the month.

SELECT  bus_id
        , ending_mileage
        , (
            SELECT MAX(bus_mileage)
            FROM @mileage m
            WHERE DATEPART(year, m.service_date) = DATEPART(YEAR, DATEADD(MONTH, -1, bus_mileage.last_service_date))
                AND DATEPART(MONTH, m.service_date) = DATEPART(MONTH, DATEADD(MONTH, -1, bus_mileage.last_service_date)) 
                AND m.bus_id = bus_mileage.bus_id
        ) AS 'starting_mileage'
        , mileage_year
        , mileage_month
FROM (
    SELECT bus_id
        , MAX(bus_mileage) AS 'ending_mileage'
        , DATEPART(YEAR, service_date) AS 'mileage_year'
        , DATEPART(MONTH, service_date) AS 'mileage_month'
        , MAX(service_date) AS 'last_service_date'
    FROM @mileage
    GROUP BY bus_id
        , DATEPART(YEAR, service_date)
        , DATEPART(MONTH, service_date)
) bus_mileage

Then I would wrap the whole thing in an outer query that subtracts the starting mileage from the ending mileage.

SELECT  bus_id
      , ending_mileage
      , starting_mileage
      , ending_mileage - starting_mileage AS 'mileage_for_the_month'
      , mileage_year
      , mileage_month
FROM (
    SELECT  bus_id
          , ending_mileage
          , (
                SELECT MAX(bus_mileage)
                FROM @mileage m
                WHERE DATEPART(year, m.service_date) = DATEPART(YEAR, DATEADD(MONTH, -1, bus_mileage.last_service_date))
                    AND DATEPART(MONTH, m.service_date) = DATEPART(MONTH, DATEADD(MONTH, -1, bus_mileage.last_service_date)) 
                    AND m.bus_id = bus_mileage.bus_id
          ) AS 'starting_mileage'
          , mileage_year
          , mileage_month
    FROM (
        SELECT bus_id
            , MAX(bus_mileage) AS 'ending_mileage'
            , DATEPART(YEAR, service_date) AS 'mileage_year'
            , DATEPART(MONTH, service_date) AS 'mileage_month'
            , MAX(service_date) AS 'last_service_date'
        FROM @mileage
        GROUP BY bus_id
            , DATEPART(YEAR, service_date)
            , DATEPART(MONTH, service_date)
    ) bus_mileage
) monthly_mileage

Here is the whole thing with demo data so you can see how it all works together.

DECLARE @mileage TABLE(
    service_date DATE NOT NULL
  , bus_id  INT NOT NULL
  , bus_mileage INT NOT NULL
)
INSERT INTO @mileage (service_date, bus_id, bus_mileage)
VALUES ('7/1/2018', 202, 149654)
    , ('7/15/2018', 202, 151502)
    , ('8/1/2018', 202, 152891)
    , ('8/15/2018', 202, 153502)
    , ('9/3/2018', 202, 154891)
    , ('10/15/2018', 202, 155502)
    , ('11/3/2018', 202, 157891)
    , ('7/5/2018', 302, 155502)
    , ('8/3/2018', 302, 157691)

SELECT  bus_id
      , ending_mileage
      , starting_mileage
      , ending_mileage - starting_mileage AS 'mileage_for_the_month'
      , mileage_year
      , mileage_month
FROM (
    SELECT  bus_id
          , ending_mileage
          , (
                SELECT MAX(bus_mileage)
                FROM @mileage m
                WHERE DATEPART(year, m.service_date) = DATEPART(YEAR, DATEADD(MONTH, -1, bus_mileage.last_service_date))
                    AND DATEPART(MONTH, m.service_date) = DATEPART(MONTH, DATEADD(MONTH, -1, bus_mileage.last_service_date)) 
                    AND m.bus_id = bus_mileage.bus_id
          ) AS 'starting_mileage'
          , mileage_year
          , mileage_month
    FROM (
        SELECT bus_id
            , MAX(bus_mileage) AS 'ending_mileage'
            , DATEPART(YEAR, service_date) AS 'mileage_year'
            , DATEPART(MONTH, service_date) AS 'mileage_month'
            , MAX(service_date) AS 'last_service_date'
        FROM @mileage
        GROUP BY bus_id
            , DATEPART(YEAR, service_date)
            , DATEPART(MONTH, service_date)
    ) bus_mileage
) monthly_mileage

Not sure if this meets your requirements exactly, but this is how I would report on mileage based on the information you've given me.

Upvotes: 1

Related Questions