Click here to Skip to main content
15,068,646 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
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:
SQL
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.
Posted
Updated 7-Jun-21 0:14am
Comments
Boipelo 23-Mar-12 6:24am
   
Let me clarify something… I using select because I want to check if there are any rows before saving.
Member 11811228 3-Jul-15 7:13am
   
i need a coding in all of three i.e jsp,html and sql for shar fire call in one program
Boipelo 24-Jul-15 13:38pm
   
Solution 1 has is your answer. No jsp needed.

Try below and let me know if this works for your requirement.

SQL
SELECT * FROM MyTable
  WHERE ((StartDate > @StartDate AND EndDate < @StartDate) -- Starts within an already used time slot
    OR 
(StartDate < @EndDate AND EndDate > @EndDate))-- Ends within an already used time slot
   
Comments
Boipelo 26-Mar-12 5:16am
   
Thanks for taking your time to help me, I have posted the final solution.
Saral S Stalin 26-Mar-12 6:09am
   
No probs Bro.. Happy to help :)
@Saral S Stalin, thanks for your assistance... your solution works with these minor adjustments

SQL
SELECT * FROM MyTable
 WHERE ((StartDate <= @StartDate AND EndDate > @StartDate) -- Included the start date to avoid duplicates start date and also changed the '>' to '<' after AND, it was not within
  OR 
  (StartDate < @EndDate AND EndDate >= @EndDate))-- The range was perfect, I just included the EndDate so that we dont have events ending at the same time.

Thanks once more for your solution... you gave me direction
   
v3
You can check if two dates lies between two dates by following query

SELECT * FROM [vacation]
where ((DATEDIFF(d,'2021-06-30',startdate) <= 0 and DATEDIFF(d,'2021-07-10',enddate) >= 0) or
(DATEDIFF(d,'2021-06-30',startdate) <= 0 and DATEDIFF(d,'2021-07-10',enddate) >= 0))
OR ((startdate between '2021-06-30' and '2021-07-10') or (enddate between '2021-06-30' and '2021-07-10'))
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900