check for date / time overlap between records of two different tables

Question

check for date / time overlap between records of two different tables

Stoutheart

Valued Member

Points: 53
More actions
August 8, 2016 at 10:53 pm

#328932

I have a table full of events with dates, start times, and stop times. Many of these events happen at the same time. I have another table that contains all the events a user has already signed up for, including the dates, start times, and stop times.
EventTable (EventID, EventDate, EventStartTime, EventStopTime)
RegisteredTable (EventID, UserID, EventDate, EventStartTime, EventStopTime)
I want to select all the events from EventTable that do not exist in RegisteredTable, but also do not have start and stop time overlaps with events in RegisteredTable. Basically, I want to see all the events a user can still sign up for that don't have a date or time conflict.
Please help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

please provide sample data and expected results......see here for how to do that https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Stoutheart (8/8/2016)
I have a table full of events with dates, start times, and stop times. Many of these events happen at the same time. I have another table that contains all the events a user has already signed up for, including the dates, start times, and stop times.
EventTable (EventID, EventDate, EventStartTime, EventStopTime)
RegisteredTable (EventID, UserID, EventDate, EventStartTime, EventStopTime)
I want to select all the events from EventTable that do not exist in RegisteredTable, but also do not have start and stop time overlaps with events in RegisteredTable. Basically, I want to see all the events a user can still sign up for that don't have a date or time conflict.
Please help!

WHERE Event.EventStartDateTime < Registered.EventStopDateTime

OR Registered.EventStartDateTime < Event.EventStopDateTime

Please note - those are DateTime values for EventStart and EventStop.

I leave with you the task of fixing the stupidity of date and time separation in the database.

_____________
Code for TallyGenerator

cmullen77 29296 Valued Member Points: 71 More actions · Answer 3

Hello Stoutheart,

Since you may have multiple events in a given day, for a given user, you may want to look at this a bit differently..

I would query to get the time ranges that the person still has available (open) and then see if any events have a start/stop time that falls between my open times...

I'm hoping that no events span days...

Its kind of like the query to get time pairs for a timeclock query, except you want the times NOT worked...

Make sense?