|
|
Posted by serge on 09/24/06 16:48
/*
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.databases.ms-sqlserver/browse_thread/thread/5271bf273003ffde/5f580af5913efcce?lnk=st&q=between+startdate+enddate+group%3Acomp.databases.ms-sqlserver&rnum=20&hl=en#5f580af5913efcce
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/817c05b1a89cd020/a439bd3af4345ac1?lnk=st&q=&rnum=5&hl=en#a439bd3af4345ac1
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
*/
Navigation:
[Reply to this message]
|