SEO

/*
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?

You can copy/paste this whole post in SQL Query Analyzer or Management
Studio and
run it once you've made sure there is no harmful code.

I am working on an existing database where there is code that is using
BETWEEN logic and three different OR conditions to search for a user that
has worked between begin and end date parameters that you search for.
For me the three WHERE conditions with the Begin and End dates are a little
confusing so I would like to know if there's a better/simpler way to write
this.

1- I have groups table with GroupID, Name
2- I have employees table with EmployeeID, LastName, FirstName
3- I have employeegroups table where the EmployeeID has the GroupID he/she
was/is a member of and from what Begin to what End dates.
The employee can never be a member of two groups in any date interval.
The employee always was/is a member of a group from a certain to a certain
date and then the next group he/she is a member of a group begins 1 date
after the previous group membership's end date. Therefore If I worked from
2006-01-01 to 2006-01-31 and then I changed group, well in this database
the
next group dates would begin at 2006-02-01 till an Open Ended default date
of
2009-12-31.
I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05

Please continue to read below at the bottom.

*/

USE tempdb
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype
= 'U')
BEGIN
TRUNCATE TABLE EmployeeGroups
DROP TABLE EmployeeGroups
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')
BEGIN
TRUNCATE TABLE Groups
DROP TABLE Groups
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype =
'U')
BEGIN
TRUNCATE TABLE Employees
DROP TABLE Employees
END
GO


CREATE TABLE dbo.Groups
(
GroupID int NOT NULL,
Name varchar(50) NOT NULL
CONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED
(
GroupID
)
)
GO

CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL
CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED
(
EmployeeID
)
)
GO

CREATE TABLE dbo.EmployeeGroups
(
EmployeeID int NOT NULL,
GroupID int NOT NULL,
BeginDate datetime NOT NULL,
EndDate datetime NOT NULL,
CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED
(
EmployeeID,
GroupID
),
CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES Employees(EmployeeID),
CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY
(
GroupID
) REFERENCES Groups(GroupID)
)
GO

INSERT Groups (GroupID, Name)
SELECT 1, 'Group1' UNION ALL
SELECT 2, 'Group2' UNION ALL
SELECT 3, 'Group3' UNION ALL
SELECT 4, 'Group4'
GO

INSERT Employees (EmployeeID, LastName, FirstName)
SELECT 1, 'Davolio', 'Nancy' UNION ALL
SELECT 2, 'Fuller', 'Andrew' UNION ALL
SELECT 3, 'Leverling', 'Janet' UNION ALL
SELECT 4, 'Peacock', 'Margaret' UNION ALL
SELECT 5, 'Buchanan', 'Steven'
GO

INSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)
SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALL
SELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALL
SELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALL
SELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALL
SELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALL
SELECT 4, 3, '2006-05-17', '2099-12-31'
GO

--SELECT * FROM Groups
--SELECT * FROM Employees
--SELECT * FROM EmployeeGroups

DECLARE @EmployeeID INTEGER
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

PRINT 'First example of querying...'
SET @EmployeeID = 1
SET @BeginDate = 'Sep 18 2005 12:00:00:000AM'
SET @EndDate = 'Sep 24 2006 12:00:00:000AM'

-- 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)
)


PRINT 'Second example of querying...'
SET @EmployeeID = 1
SET @BeginDate = 'Sep 18 2003 12:00:00:000AM'
SET @EndDate = 'Oct 16 2004 12:00:00:000AM'

-- 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)
)



-- For me I would swap the parameters and the EmployeeGroups.BeginDate and
-- EmployeeGroups.EndDate like this because it's easier for me to understand
the code
-- this way.
PRINT 'Third example of querying the same parameters of the second
example...'
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @EmployeeID
AND (
(@BeginDate >= EmployeeGroups.BeginDate AND @EndDate <=
EmployeeGroups.EndDate)
OR (@BeginDate <= EmployeeGroups.BeginDate AND @EndDate >=
EmployeeGroups.BeginDate)
OR (@BeginDate <= EmployeeGroups.EndDate AND @EndDate >=
EmployeeGroups.EndDate)
)

/*
I was doing some research on the newsgroup and I saw some similar examples
where Celko and David
Portas where using or discussing about using calendar tables:

http://groups.google.com/group/comp....580af5913efcce

http://groups.google.com/group/comp....bd3af4 345ac1

First I am not sure if those examples can be applied to my scenario and also
I am not
sure how to use and whether or not I should be using a calendar table in
this case?

My question is there a better/simpler way to write these WHERE conditions or
the
whole statement?

I would appreciate any help on this.

Thank you

*/

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

> 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.

Thanks Hugo. I actually drew all the possible intervals and I had
16 possibilities.

In any case your example is much easier to grasp. I'll work on
memorizing the logic so that I'm able to write this again without
reading the explanation.