Hello all,
I'm having trouble creating a MySQL query that will pull the data that I need into a recordset.
I've designed an application that tracks transit routes. There is table 'tripslog' that logs the route name [tripName], [startTime], [end time], [changeType] ('NEW', 'CHG', or 'DELT'), and [lastUpdate] (date) fields. Essentially an audit log of transit route data over time.
Each distinct route is identified by a tripName that matches a specific pattern.
Route CR01A, CR01B, CR01C
BR01A, BR01B
which all have different last updates.
I have a program loop that calculates the amount of time each vehicle incurred on each day. So, I iterate through currentdate(x).
So, I need a mysql query to put into a recordset each most recent route like 'CR01%' that has the highest lastUpdate that is less than or equal to currentdate(x), and has not been DELETED (ie the most recent distinct tripName changetype <> 'DELT'.
So, find me all routes in the triplog table like 'CR01%' each with the highest current date that is also less than the currentdate(x) in the program loop, and has not been most recently deleted.
Basically - on any given day, find me the most recent trip data on that day associated with this vehicle.
Any idea on how to write a query like this? Would be into a recordset, so
Set recordsetname = dbname.openrecordset("SELECT...")
Thanks,
K
I'm having trouble creating a MySQL query that will pull the data that I need into a recordset.
I've designed an application that tracks transit routes. There is table 'tripslog' that logs the route name [tripName], [startTime], [end time], [changeType] ('NEW', 'CHG', or 'DELT'), and [lastUpdate] (date) fields. Essentially an audit log of transit route data over time.
Each distinct route is identified by a tripName that matches a specific pattern.
Route CR01A, CR01B, CR01C
BR01A, BR01B
which all have different last updates.
I have a program loop that calculates the amount of time each vehicle incurred on each day. So, I iterate through currentdate(x).
So, I need a mysql query to put into a recordset each most recent route like 'CR01%' that has the highest lastUpdate that is less than or equal to currentdate(x), and has not been DELETED (ie the most recent distinct tripName changetype <> 'DELT'.
So, find me all routes in the triplog table like 'CR01%' each with the highest current date that is also less than the currentdate(x) in the program loop, and has not been most recently deleted.
Basically - on any given day, find me the most recent trip data on that day associated with this vehicle.
Any idea on how to write a query like this? Would be into a recordset, so
Set recordsetname = dbname.openrecordset("SELECT...")
Thanks,
K
Last edited: