SQL overlapping dates

Question

Here, we have many meetings during the day. Usually from 9:00am-11:30am, 1:00pm-3:30pm, 4:00pm-6:30pm.

I'm having a hard time scheduling these meetings. I don't get how to check if they overlap for the same person who is leading the meeting.

Ex: Should raise an error if Person1 has a meeting scheduled for 10:00am- 11:00am and another for 10:45am-11:30am.

I've come to this solution, but it is only partially working:

if exists (
    select 1
    from
        Meeting M
    where
        M.IdPerson = @IdPerson --Stored procedure parameter
        and 
            (@StartDate <= M.EndDate or  -- 1
            @EndDate >= M.StartDate or  -- 2
            (@StartDate > M.StartDate and @DtFim < A.Data_Termino))) -- 3
    return 1

In the image below, I explain the three conditions in the where clause. The green lines are the new meeting start and end dates. The red lines are the old's.

How can I make this work?

How about some ddl and sample data? sqlfiddle.com is a great place to start. — Sean Lange, Sep 05 '14 at 21:21
You may be interested in [this post](http://stackoverflow.com/a/12043793/533120). — Branko Dimitrijevic, Sep 05 '14 at 21:26

score 3 · Accepted Answer · answered Sep 05 '14 at 21:24

3

I think the logic you want is:

if exists (select 1
           from Meeting M
           where M.IdPerson = @IdPerson and --Stored procedure parameter
                 @StartDate <= M.EndDate and
                 @EndDate >= M.StartDate
          )
    return 1

(I'm not sure if you want <= or < for these comparisons.) This assumes that StartDate and EndDate also store the time component.

Two time periods overlaps if the first starts before the second ends, and it ends after the second starts.

answered Sep 05 '14 at 21:24

Gordon Linoff

1,242,037
58
646
786

The general logic is correct. However, if both date and time are involved, then the range should be stored as a half-open interval `[start,end)`. Since both comparisons involve an end value in a range-overlap query, then *neither* should test for equality. Use `<` and `>`. – Matt Johnson-Pint Sep 05 '14 at 21:28
Also note that if these were date-only ranges, then fully-closed intervals would be more appropriate and you would use `<=` and `>=`. – Matt Johnson-Pint Sep 05 '14 at 21:29
@MattJohnson . . . The OP suggests that the date/time are actually discreet values at half hour intervals. What I don't know is whether or not 9:00 a.m. - 10:00 a.m. overlaps with 10:00 a.m. - 10:30 a.m. Your points about inequalities and date/times are certainly true, though. – Gordon Linoff Sep 06 '14 at 01:42
I suppose it's possible that someone might want those to overlap, but in the vast majority of cases I've seen, those would be adjacent and non-overlapping. Especially for meeting times. That's why I recommend the half-open interval. Cheers! – Matt Johnson-Pint Sep 06 '14 at 18:31

SQL overlapping dates

1 Answers1