|
|
Posted by Hugo Kornelis on 09/24/06 22:11
On Sun, 24 Sep 2006 12:48:20 -0400, serge wrote:
>/*
>Subject: How best to use BETWEEN Begin and End Dates to find out if an
>employee
>was/is member of any group for a certain date range?
(snip)
>-- This is the code logic being used in the database I am looking at.
>SELECT *
>FROM EmployeeGroups
>WHERE EmployeeGroups.EmployeeID = @EmployeeID
> AND (
> (EmployeeGroups.BeginDate <= @BeginDate AND EmployeeGroups.EndDate
> >= @EndDate)
> OR (EmployeeGroups.BeginDate >= @BeginDate AND
>EmployeeGroups.BeginDate <= @EndDate)
> OR (EmployeeGroups.EndDate >= @BeginDate AND EmployeeGroups.EndDate <=
>@EndDate)
> )
(snip)
>My question is there a better/simpler way to write these WHERE conditions or
>the
>whole statement?
Hi Serge,
First of all: thanks for providing CREATE TABLE and INSERT statements
with your question. That made it very easy to understand your question
and to test my reply before posting it.
Second: Yes, this can be made much simpler. I've struggled with the
"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap. It turned
out that there are many ways in which two intervals can overlap, but
only two ways in which they can not overlap - if interval A ends before
interval B starts, or if interval A starts after interval B ends. So the
intervals do NOT overlap IF A.end < B.start OR A.start > B.end (note:
you must change < and > to <= and >=, depending on your definition of
"overlap"). Reverse this to find that the condition for overlapping
intervals is simple IF A.end >= B.Start AND A.start <= B.end
Or, in your query:
SELECT *
FROM EmployeeGroups
WHERE EmployeeID = @EmployeeID
AND BeginDate <= @EndDate
AND EndDate >= @BeginDate
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|