The following will give you information pretty much as your example: car, start date, end date, distance.
SELECT m1.Car, m2.Date AS StartDate, m1.Date AS endDate, m1.od_reading - m2.od_reading AS distance FROM mileage AS m1 JOIN mileage AS m2 ON m2.rowid = (SELECT rowid FROM mileage m3 WHERE m3.Date < m1.Date AND m3.car = m1.car ORDER BY Date DESC LIMIT 1) WHERE Distance IS NOT NULL; You can add an additional "AND CAR ='foo'" if you only want data about foo. Note: this uses the rowid, which is a SQLite specific feature, as a match on the join because it's slightly more efficient. You could also have the join condition stated in more general SQL terms as: JOIN mileage AS m2 ON m2.car = m1.car AND m2.Date = (SELECT Date FROM mileage m3 WHERE m3.Date < m1.Date AND m3.car = m1.car ORDER BY Date DESC LIMIT 1) On Thu, 20 Dec 2018 at 19:48, Jungle Boogie <jungleboog...@gmail.com> wrote: > 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
