I am building a scheduling app, where I assign schedules to certain members but those schedules can change at a certain date or be assigned for the future.

My database structure is like so:

members table:

schedule table:

assigned_schedules asch table:

The effective_to and effective_from values are a unix timestamp. I currently have joined the tables but my where clause is quite redundant and has errors in it. I am looking to get the schedule currently assigned for the current day of a member, or the closest assigned schedule if there is one assigned in the future. A member can have a schedule assigned to a future date and it would change at that time, but I am looking for a daily view.

Currently, I join all the tables and have this where clause (where the ? is today's timestamp):

( asch.effective_from <= ? AND ( asch.effective_to IS NULL OR asch.effective_to >= ? ) OR asch.effective_from IS NULL ) XOR ( ( asch.effective_from > ? AND asch.effective_to IS NULL ) XOR asch.effective_from > ? )

assigned_to value can be null, which means that the schedule has no termination time.

  • Is effective_from always before asch.effective_to? That would simplify the query.
    – Ken Kim
    Apr 25, 2018 at 18:00
  • @KenKim what do you mean by before? It does not have to be, no. I just need a way to get one record for each member, really.
    – Ice76
    Apr 25, 2018 at 18:18


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.