Click here to Skip to main content
14,971,029 members
Home / Discussions / Database
   

Database

 
GeneralRe: Get columns name Pin
Victor Nijegorodov4-Jun-21 9:47
MemberVictor Nijegorodov4-Jun-21 9:47 
AnswerRe: Get columns name Pin
Mycroft Holmes4-Jun-21 12:43
professionalMycroft Holmes4-Jun-21 12:43 
QuestionSQL statements Pin
Erick Kaira27-May-21 8:09
MemberErick Kaira27-May-21 8:09 
AnswerRe: SQL statements Pin
Victor Nijegorodov27-May-21 8:40
MemberVictor Nijegorodov27-May-21 8:40 
AnswerRe: SQL statements Pin
SeanChupas27-May-21 9:02
MemberSeanChupas27-May-21 9:02 
QuestionRe: SQL statements Pin
David Crow27-May-21 9:12
MemberDavid Crow27-May-21 9:12 
AnswerRe: SQL statements Pin
Mycroft Holmes27-May-21 12:19
professionalMycroft Holmes27-May-21 12:19 
QuestionSQL query dilemma Pin
David Crow20-May-21 10:31
MemberDavid Crow20-May-21 10:31 
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_schedules table and multiple rows are added to the schedule_intervals table 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 the service_schedules table and 33 related rows would be added to the schedule_intervals table, 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 the service_schedules table and 10 related rows added to the schedule_intervals table, one for each of the 25000 mile intervals up to 250000 miles. A 'time' row would also be added to the service_schedules table and 15 related rows added to the schedule_intervals table, 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_intervals table. 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 price increase." - Harold Wilson

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles


AnswerRe: SQL query dilemma Pin
Mycroft Holmes20-May-21 13:05
professionalMycroft Holmes20-May-21 13:05 
QuestionAccounting problem in SQL Pin
Member 1400680615-May-21 23:23
MemberMember 1400680615-May-21 23:23 
AnswerRe: Accounting problem in SQL Pin
Mycroft Holmes16-May-21 12:17
professionalMycroft Holmes16-May-21 12:17 
AnswerRe: Accounting problem in SQL Pin
CHill6019-May-21 2:55
mveCHill6019-May-21 2:55 
QuestionSQL Server Execution Timeout Expired Pin
idkd12-May-21 23:00
Memberidkd12-May-21 23:00 
AnswerRe: SQL Server Execution Timeout Expired Pin
SeanChupas13-May-21 1:49
MemberSeanChupas13-May-21 1:49 
QuestionIn-memory database with acid transactions and high availability Pin
Mathieu Seillier12-May-21 2:54
MemberMathieu Seillier12-May-21 2:54 
AnswerRe: In-memory database with acid transactions and high availability Pin
Mycroft Holmes12-May-21 13:00
professionalMycroft Holmes12-May-21 13:00 
AnswerRe: In-memory database with acid transactions and high availability Pin
Richard MacCutchan12-May-21 21:08
mveRichard MacCutchan12-May-21 21:08 
QuestionSQL Server round trip issue between server and client pc Pin
Mou_kol1-May-21 6:35
MemberMou_kol1-May-21 6:35 
AnswerRe: SQL Server round trip issue between server and client pc Pin
Mycroft Holmes1-May-21 11:57
professionalMycroft Holmes1-May-21 11:57 
AnswerRe: SQL Server round trip issue between server and client pc Pin
Gerry Schmitz1-May-21 17:12
mveGerry Schmitz1-May-21 17:12 
GeneralRe: SQL Server round trip issue between server and client pc Pin
Mycroft Holmes2-May-21 12:24
professionalMycroft Holmes2-May-21 12:24 
GeneralRe: SQL Server round trip issue between server and client pc Pin
Gerry Schmitz2-May-21 18:53
mveGerry Schmitz2-May-21 18:53 
QuestionSqlServer (and ADO.NET) mystery... Pin
Super Lloyd27-Apr-21 19:19
MemberSuper Lloyd27-Apr-21 19:19 
AnswerRe: SqlServer (and ADO.NET) mystery... Pin
Richard Deeming27-Apr-21 22:11
mveRichard Deeming27-Apr-21 22:11 
GeneralRe: SqlServer (and ADO.NET) mystery... Pin
Super Lloyd27-Apr-21 22:42
MemberSuper Lloyd27-Apr-21 22:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.