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
