How to compare Starting time and End time of a event with existing events of same time on same day?

Question

I am using MS SQL server 2008. Here I need to compare the newly entered start time and End time of an event with the existing event timings. In the Database I need to check the new Starting time and End time values and the time Period should not match with the existing/ already booked event timings.

I need to check this condition in the Database stored procedure, if the condition is satisfied then only accept the event. Best example is Conference hall booking.

May be this will help you: http://stackoverflow.com/questions/5218370/sql-overlaps-operator-problem-how-to-get-rid-of-it — Karloss, Jul 18 '13 at 12:08

Raj · Accepted Answer · 2013-07-24T06:57:10.777

SELECT @count = COUNT (EventID) FROM TblEvent WHERE (('@STARTTIME'  BETWEEN StartTime AND EndTime) or ('@ENDTIME' BETWEEN StartTime AND EndTime));  
IF @count = 0
    Begin
    Select @count = COUNT (EventID) FROM TblEvent WHERE (('@STARTTIME' < StartTime and '@ENDTIME' > EndTime));
    END
        IF @count > 0 
            BEGIN
                SELECT 'Event is Already Exists at that point of time please select another Time ' AS 'MESSAGE';    
            END
    Else
    BEGIN
      //ADD QUERY TO SAVE THE THE EVENT IN DB// 
        SELECT 'Event is Added' AS 'MESSAGE';   
    END

NOTE:

@STARTTIME = USER ENTERED START TIME,

@ENDTIME =USER ENTERED END TIME

score 0 · Answer 2 · answered Jul 18 '13 at 12:06

0

You can use 24 hour format to solve this in your logic. you have to save existing event timings in Database. whenever new event timings entered, you need to compare with existing both start & end timings with previous ones.

For Example:

Event1: x1 to y1

Event2; x2 to y2

if(x2==x1 && y2==y1)

if(x2>x1 &&x2<y1) andif(y2<y1)

...so on based on your requirement.

answered Jul 18 '13 at 12:06

siri

71
5

I tried your logic, but it is not satisfying the condition. Let me explain the condition. If there are events, Event 1 from 10:00 AM to 1:00 PM and Event 2 From 2:00PM to 5:00PM. If we user is trying to Book the hall with new timings, my code should not accept timings (Like 9:00AM to 12:00PM, 2:00PM to 4:00PM, 9:00AM to 6:00PM etc..) which are already booked. – Sanjeev4evr Jul 18 '13 at 12:50

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

Answer is (StartA <= EndB) and (EndA >= StartB). Please, read these: Determine Whether Two Date Ranges Overlap

edited May 23 '17 at 12:12

Community

1
1

answered Jul 18 '13 at 13:11

Karloss

817
3
9
27

score 0 · Answer 4 · answered Jul 18 '13 at 14:06

0

Hey Sanjeev try the following code..!

Select * From your Event

WHERE ('A' BETWEEN StartTime AND EndTime) or ('B' BETWEEN StartTime AND EndTime)

Note: Here A and B are your Start time and End Time,

and Italics are your database values.

answered Jul 18 '13 at 14:06

Raj

26
2

What about A is before _StartTime_ and B is after the _Endtime_; how to compare these conditions ? – Sanjeev4evr Jul 18 '13 at 14:09

score 0 · Answer 5 · answered Jul 19 '13 at 08:55

If I had understood correctly, this should solve the problem.

Let us take A and B are your start time. X and Y are previously in Database.

Then (A,B) does not over lap (X,Y) when X > B or Y < A this can be told as the new event must finish before the previous one or it must start after the previously available one. So query must be

...
Where NewStartTime > EndTime or NewEndTime < StartTime;

How to compare Starting time and End time of a event with existing events of same time on same day?

5 Answers5

For Example: