CODEWITHSUNDEEP
sqlsqlite
tomwassing
tomwassing

Reputation: 961

How to calculate distance driven by day based on mileage

I am trying to calculate the distance driven based on the mileage. I am grabbing the MAX and MIN distance per day and subtract that from each other. However this does not use the 'last' mileage from the previous day, so a piece of 'distance' is missing.

How can I grab the 'latest' mileage before the given day and use that as the MIN mileage instead.

SQL Fiddle: http://www.sqlfiddle.com/#!7/3ea7d/7/0

PS: In this fiddle I use a simple 'day' stored as a integer, however in the real implementation this is a date stored as epoch which is then converted to day by strftime.

Desired results:

day      distance
1        300
2        400
3        400

Upvotes: 0

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Assuming you want the previous day's maximum, you can use a correlated subquery:

WITH td as (
      SELECT t.day, MAX(t.distance) as maxd, MIN(t.distance) mind
      FROM trips t
      GROUP BY t.day
     )
SELECT td.day,
       (maxd -
        COALESCE( (SELECT MAX(td2.maxd)
                   FROM td td2
                   WHERE td2.day < td.day
                  ),
                  td.mind
                )
       ) as diff
FROM td;

Most databases support the ANSI standard LAG() function, which would make this much easier. Alas, SQLite does not support it.

Upvotes: 1

Related Questions