04-04-02, 08:51 #1Registered User
- Join Date
- Mar 2002
Unanswered: How to check for entries lying within certain ranges
Not sure if that's the best way to describe the subject but here's the question.
I need to represent timetable data in the database. At the moment I have a table for the Timetable itself, when its valid from and till etc.
I have another table representing an individual entry on this timetable, i.e. when it starts, finishes, the timetable its on, and the day of the week it occurs. This table also refers to an activity through ActivityID.
The final table represents an activity, with basic information about it, description etc.
I want to make sure that users can't enter in activities into a timetable when activities are already scheduled for that day, on that timetable. Originally I did plan to use a constraint that would check against minimum and maximum values for a particular timetable and day. However, I then realised that that would not take account for when there's more than 1 activity on a particular day. It would only check to see if it lay between the minimum and maximum. Of course, if there were 2 hour long sessions first and last thing, there would be a large amount of idle time in the middle that could be filled, but wouldn't be accounted for.
Has anyone got any suggestions about how this could likely be achieved?
04-08-02, 14:43 #2Registered User
- Join Date
- Feb 2002
- Houston, TX
Paul, If I understand you correctly, it's okay for your users to schedule an activity as long as it will not be concurrently running with another task of the same activity type. If that is so then your task is to look for records where the requested start time falls between an existing start and end time OR the requested end time falls between an existing start and end time.Paul Young
(Knowledge is power! Get some!)