SQL query dilemma
-
I'm working on a personal app to help me better manage my family's car maintenance schedules. These are two of the tables being used and their relationship: Schema. For a given car, a schedule is created where at least one row is added to the
service_schedulestable and multiple rows are added to theschedule_intervalstable depending on the options chosen. One example would be if a "rotate tires every 7500 miles" schedule was created, one row would be added to theservice_schedulestable and 33 related rows would be added to theschedule_intervalstable, one for each of the 7500 mile intervals up to 250000 miles. Another example would be if a "change oil every 25000 miles or 12 months" schedule was created, one 'mileage' row would be added to theservice_schedulestable and 10 related rows added to theschedule_intervalstable, one for each of the 25000 mile intervals up to 250000 miles. A 'time' row would also be added to theservice_schedulestable and 15 related rows added to theschedule_intervalstable, one for each year up to 15 years. With this schema, I can easily do 'past due services' and 'upcoming services' queries. For example, I can find all upcoming services for vehicle 4 with current mileage of 163451 using:SELECT * FROM schedule_intervals WHERE schedule_id IN (SELECT _id FROM service_schedules WHERE vehicle_id = 4) AND (163451 < mileage OR 1621540799076 < date)
For vehicle 4, it has six service schedules for things like oil/filter (mileage or date), tires (mileage), spark plugs (mileage), transmission (mileage), etc, all of which create 155 rows in the
schedule_intervalstable. Using the above query, 114 rows are returned for any upcoming services. The issue I'd like to resolve, if possible, is to only show one of each service type (one of mileage and one of date). In other words, instead of showing all of the oil/filter services that are due (175000, 200000, 225000, and 250000, April 2022, April 2023, April 2024, ..., April 2036), I'd like to just show the next one of each type (175000, April 2022), as all other ones past that are irrelevant. At this point, I don't know if I need to modify either of the two tables, and/or add a bit more complexity to the query. Thoughts or ideas? Thanks. DC"One man's wage rise is another man's pri
-
I'm working on a personal app to help me better manage my family's car maintenance schedules. These are two of the tables being used and their relationship: Schema. For a given car, a schedule is created where at least one row is added to the
service_schedulestable and multiple rows are added to theschedule_intervalstable depending on the options chosen. One example would be if a "rotate tires every 7500 miles" schedule was created, one row would be added to theservice_schedulestable and 33 related rows would be added to theschedule_intervalstable, one for each of the 7500 mile intervals up to 250000 miles. Another example would be if a "change oil every 25000 miles or 12 months" schedule was created, one 'mileage' row would be added to theservice_schedulestable and 10 related rows added to theschedule_intervalstable, one for each of the 25000 mile intervals up to 250000 miles. A 'time' row would also be added to theservice_schedulestable and 15 related rows added to theschedule_intervalstable, one for each year up to 15 years. With this schema, I can easily do 'past due services' and 'upcoming services' queries. For example, I can find all upcoming services for vehicle 4 with current mileage of 163451 using:SELECT * FROM schedule_intervals WHERE schedule_id IN (SELECT _id FROM service_schedules WHERE vehicle_id = 4) AND (163451 < mileage OR 1621540799076 < date)
For vehicle 4, it has six service schedules for things like oil/filter (mileage or date), tires (mileage), spark plugs (mileage), transmission (mileage), etc, all of which create 155 rows in the
schedule_intervalstable. Using the above query, 114 rows are returned for any upcoming services. The issue I'd like to resolve, if possible, is to only show one of each service type (one of mileage and one of date). In other words, instead of showing all of the oil/filter services that are due (175000, 200000, 225000, and 250000, April 2022, April 2023, April 2024, ..., April 2036), I'd like to just show the next one of each type (175000, April 2022), as all other ones past that are irrelevant. At this point, I don't know if I need to modify either of the two tables, and/or add a bit more complexity to the query. Thoughts or ideas? Thanks. DC"One man's wage rise is another man's pri
You need to make the query quite a bit more complex. Probably half a dozen based on each vehicle and service type and the date and the mileage Select Top 1 fieldname from tablename order by (date or mileage) filter by vehicle and service type. You can then either pivot the results or accept multiple rows.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
