> On 20 Dec 2018, at 4:21 pm, Jungle Boogie <jungleboog...@gmail.com> wrote: > > Hi All, > > This is more of a how do I do this in sql question. I apologize in advance > for a simple question, but I need to learn somehow, so any pointers are > appreciated. > > My very simple schema: > > CREATE TABLE mileage ( > date text, > "car" text, > "od_reading" integer > ) > > 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 can select the first and last od_readings pertaining to a particular car > this > way: > select od_reading from mileage where car='foo' limit 1 > select od_reading from mileage where car='bar' order by od_reading desc limit > 1 > > How do I subtract the last query from the first query to determine how many > miles were driven? > > Next, is there an elegant way to see how many miles difference there is > between > two readings of the same car? > > For instance, how many miles difference are there between bar on 2018/05/20 > and > 2018/05/06?
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, > 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
