August 8, 2016 at 10:53 pm
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!
August 9, 2016 at 1:50 am
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
August 10, 2016 at 4:55 pm
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
August 10, 2016 at 7:50 pm
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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply