CODEWITHSUNDEEP
sqlsql-serveralgorithmdate
BigBenne
BigBenne

Reputation: 29

DateTime overlap 3 dates

I am trying to set up an algorithm to detect conflicts between several dates. But I have a problem which is the following:

I have blocks that are programmed on a given date. And I try to detect conflicts. The goal is not to see more than two blocks at the same time

I try to bring up the cases where there are more than two conflicts. I currently use this method to find a conflict between two dates:

Datestart <= @DateEndToTest AND DateEnd >= @DateStartToTest

The problem is that I have three conflicts for during one second. Here is a diagram to explain it better:

How to avoid detecting three conflicts at 00:00?

Thanks in advance

The problem is that I always have three conflicts at one time

B1 -> start: 2021-12-24 00:00:00 | end: 2021-12-25 00:00:00
B2 -> start: 2021-12-25 00:00:00 | end: 2021-12-26 00:00:00
B3 -> start: 2021-12-24 00:00:00 | end: 2021-12-26 00:00:00
(b3 covers b1 and b2)

Three conflicts at 2021-12-25 00:00:00 but I don't want to detect this conflict.

Upvotes: 0

Views: 60

Answers (1)

Carra
Carra

Reputation: 17964

To exclude one, you'll have to change your limit checks.

If you don't want the first one:

[DateStart] <= @DateTimeEnd AND [DateEnd] > @DateTimeStart

If you don't want the second one:

[DateStart] < @DateTimeEnd AND [DateEnd] >= @DateTimeStart

Upvotes: 1

Related Questions