I am not sure where to post this question, I also hope I worded it correctly.
I need help with sql, story below:
I have an table with starts and end date, including time(Like timetable). It’s working fine. I have issues conflicts.
Let’s say I have sql table with the below informa information:
StartDate: EndDate
23 March 2012 10:00pm 23 March 2012 11:00pm
23 March 2012 14:00pm 23 March 2012 16:00pm
23 March 2012 17:00pm 23 March 2012 18:00pm
I want to add an event with no conflict – that where the problem is!
Lets assume the new event has same start date, but time from 12:00 to 15:00
I am expecting a conflict as the end time (15:00) overlapse the 14:00 (second row)
Here is what I did
I checked if start date exists like
….
WHERE StartDate = @StartDate
…results
23 March 2012 14:00pm 23 March 2012 16:00pm
23 March 2012 17:00pm 23 March 2012 18:00pm
If I extend by checking if end date not between any dates
WHERE StartDate = @ StartDate
AND ((EndDate BETWEEN @ StartDate AND @EndDate OR EndDate = @ EndDate OR EndDate >@ EndDate))
It works but will have another issue when StartDate is between an event, let’s say 15pm. It's been hours trying to solve this...
Here is the procedure I wrote, but still can't avoid some duplicates:
SELECT * FROM MyTable
WHERE StartDate = @StartDate
AND ((@EndDate BETWEEN StartDate AND EndDate OR EndDate = @EndDate OR EndDate > @EndDate))
Thanking you in advance, I have googled possible solutions and cant fin one that closer to mine.