I have a view that returns a list of all bus passengers that need to be picked up on a given day (monday - sunday).
When a user is picked up, a record is written to a table named "trip_history". This table contains a date/timestamp for that ride. 2 rides are allowed per day/per rider - the ride to their destination and their ride home.
I want to add functionality to this view to restrict that list to not show riders that have already been recorded in trip_history for the AM and PM trip.
So, if the db view shows 3 riders available for pickup:
If I pick Jim up in the AM, I want the view to:
1. Check if the current system date/time is in the AM (time between 00:00:00 and 11:59:59)
2. Check the "trip_history" table to see if the latest (MAX) date/time for that rider occured between 00:00:00 and 11:59:59 of the current day
If both conditions are true, view should now show the following:
I want to repeat this process for the PM of the same day (time between 12:00:00 and 23:59:59)
The goal is to not allow for two pickups to occur for the same passenger within the morning, and conversely in the evening.
A dump of the tables is attached.
16 frilansere byr i snitt $33 for denne jobben
Hi, there i read your initial requirements and i have some question regarding to your RESTRICT VIEW RESULT [login to view URL] can we discuss it on chat ?
Hi, how are you today? I read your request and I understand the requirement. I can either modify the view or provide a query that will perform the filtering.