You can use recently supported window function (portable):
SELECT * FROM (
SELECT car, lag(date) OVER w last_date, date,
od_reading - (lag(od_reading) OVER w) diff
FROM mileage
WINDOW w AS (PARTITION BY car ORDER BY date)
) q
WHERE diff IS NOT NULL;
2018/12/21 11:48, Jungle Boogie:
> On Thu 20 Dec 2018 6:26 PM, Barry Smith wrote:
>>
>>
>>> On 20 Dec 2018, at 4:21 pm, Jungle Boogie <jungleboog...@gmail.com> wrote:
>>>
>>> Hi All,
>>>
>>> Some sample data:
>>> 2018/04/15,foo,170644
>>> 2018/04/15,bar.69625
>>> 2018/04/22,foo,170821
>>> 2018/04/22,bar,69914
>>> 2018/04/29,foo,171006
>>> 2018/04/29,bar,70123
>>> 2018/05/06,foo,171129
>>> 2018/05/06,bar,70223
>>> 2018/05/09,foo,171178
>>> 2018/05/20,foo,171304
>>> 2018/05/20,bar,70517
>>>
>>
>> I wouldn't call my solution elegant, but if you put a unique constraint on
>> the "date" column, and want your query to return null if there is not a
>> record exactly on that date:
>>
>> SELECT
>> (SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND car='bar')
>> -
>> (SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar')
>> As ExactMileage
>>
>> You can rephrase that as:
>> SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage AS
>> end WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car
>> ='bar' and start.car='bar'
>>
>> Or even you could use CTEs to repeat yourself less:
>> WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar'
>> SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar AS
>> end WHERE end.date='2018/05/20' AND start.date='2018/05/06
>>
>> If you might query between two dates that don't necessarily have a record,
>> the best you can do is give a range of what the possible driven distance may
>> be:
>> SELECT
>> (SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND
>> car='bar')
>> -
>> (SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND
>> car='bar')
>> AS MaxPossible,
>> (SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND
>> car='bar')
>> -
>> (SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND
>> car='bar')
>> AS MinPossible
>>
>> The second query finds the records before and after the dates in question
>> (or uses data on exactly that date) to figure out the minimum and maximum
>> possible distances that may have been driven between the dates of interest.
>>
>> Note that there is a more efficient form of the second query, where one
>> selects just od_reading and orders by date, with a limit of 1 instead of
>> using the MIN and MAX functions; if your table is going to be large then
>> that is a better option (with an index on (car, date), the value can then be
>> computed with an index seek instead of a table scan). I used the min/max
>> version because it's simpler and easier to read
>>
>
> Thanks all for for the helpful replies!
>
> All of them work, as expected, but I see I wasn't exactly clear with my second
> part of the question.
>
> Is it possible to determine how many miles were driven the previous week with
> current week's data in one query.
>
> For example with foo:
> 2018/05/20 - 2018/05/09 = 126
> 2018/05/09 - 2018/04/29 = 172
> 2018/04/29 - 2018/04/22 = 185
>
> Basically some kind of for loop through all the matches of 'foo'
>
>>>
>>> Thanks,
>>> j.b.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
