I'm looking for an extended answer to the question asked here:
Determine Whether Two Date Ranges Overlap
where any of the dates in either date range can be null. I've come up with the following solution, but I'm not sure if it can be simplified further.
(StartA == NULL || StartA <= EndB) &&
(EndA == NULL || EndA >= StartB) &&
(StartB == NULL || StartB <= EndA) &&
(EndB == NULL || EndB >= StartA)
Assuming:
DateTime ranges of StartA to EndA and StartB to EndB
EDIT: Sorry I quickly threw the above logic together, which seems to fail when either range's start and end dates are NULL. See David's solution below for a better & well-explained approach.
You can do this by swapping the ranges if necessary up front. Then, you can detect overlap if the second range start is: less than or equal to the first range end (if ranges are inclusive, containing both the start and end times); or. less than (if ranges are inclusive of start and exclusive of end).
let's pick the big date SELECT ID, EMP_ID, [START DATE], MAX(END DATE) FROM (SELECT ID, EMP_ID, TEAM, [END DATE], MIN([START DATE]) [START DATE] FROM my_table GROUP BY ID, EMP_ID, END_DATE ) a GROUP BY ID, EMP_ID, [START DATE] -- Now we are done with similar end date and similar start date -- At this point I will write ...
Overlap = min(A2, B2) - max(A1, B1) + 1. In other words, the overlap of two integer intervals is a difference between the minimum value of the two upper boundaries and the maximum value of the two lower boundaries, plus 1.
This case can be handled by a slight generalization of Charles Bretana's excellent answer to that question.
Let CondA Mean DateRange A Completely After DateRange B (True if StartA > EndB) Let CondB Mean DateRange A Completely Before DateRange B (True if EndA < StartB)
In this case, assuming you want a null date to represent "no starting/ending bound," the conditions are modified. For CondA
, for instance, in order for DateRange A to be completely after DateRange B, DateRange A must have a defined starting time, DateRange B must have a defined ending time, and the starting time of A must be after the ending time of B:
CondA := (StartA != null) && (EndB != null) && (StartA > EndB)
CondB
is the same with A and B switched:
CondB := (StartB != null) && (EndA != null) && (StartB > EndA)
Continuing,
Then Overlap exists if Neither A Nor B is true
Overlap := !(CondA || CondB)
and
Now deMorgan's law, I think it is, says that
Not (A Or B) <=> Not A And Not B
Overlap == !CondA && !CondB
== ![(StartA != null) && (EndB != null) && (StartA > EndB)] &&
![(StartB != null) && (EndA != null) && (StartB > EndA)]
== [(StartA == null) || (EndB == null) || (StartA <= EndB)] &&
[(StartB == null) || (EndA == null) || (StartB <= EndA)]
I think this is actually a bit more robust than the solution you developed, because if EndB == NULL
but StartA
is not null, your first condition will wind up comparing StartA <= NULL
. In most languages I'm familiar with, that's an error condition.
Without considering nulls, answer is
(StartA <= EndB) and (EndA >= StartB)
(see this for detailed explanation)
considering nulls for start and end dates,
Using C Ternary operator syntax:(StartA != null? StartA: EndB <= EndB != null? EndB: StartA) &&
(EndA != null? EndA: StartB >= StartB != null? StartB: EndA)
Or C# 4.x style null operators:
(StartA??EndB <= EndB??StartA) && (EndA??StartB >= StartB??EndA)
or in SQL:
(Coalesce(StartA, EndB) <= Coalesce(EndB, StartA))
And (Coalesce(EndA, StartB ) <= Coalesce(StartB , EndA))
Explanation:
consider the non-null answer:(StartA <= EndB) and (EndA >= StartB)
Now, consider that StartA is null, indicating that date range A has existed since beginning of time (BOT). In that case, DateRangeB can never be before DateRangeA. So first condition, (StartA(BOT) <= EndB) will ALWAYS be true, no matter what EndB is. So change this expression so that instead of comparing null with EndB, when StartA is null, compare EndB with itself
No matter what EndB is, the expression EndB <= EndB
will be true.
(We could create variables to represent BOT and EOT, but this is easier).
Do the same for other three input variables.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With