473,381 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

Better way to use BETWEEN Begin and End Dates

/*
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....39bd3af4345ac1

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

*/
Sep 24 '06 #1
2 4083
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
Sep 24 '06 #2
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.

Sep 26 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Dean | last post by:
I am a developer who works with MS SQL Server a lot, but I'm far from an expert. I am revamping an appointment scheduling system that allows for appointments to reoccur daily, weekly, monthly and...
6
by: Piotr Pietrowski | last post by:
Hello everybody, I have a *big* problem. I thought its not that big problem for you professionals... Anyway, I have a begin date which has 3 dropdown boxes (day/Month/Year). The same for the...
47
by: Jeff Relf | last post by:
Hi All, I plan on using the following C++ code to create nodes with unlimited children: // I would like to declare NodeT like this, // but it won't compile because Lnk_T is not defined yet....
2
by: velthuijsen | last post by:
The STL sort only accepts a function that is defined in the form of bool Fname(<type>, <type>) in which <type> is the type of range to be sorted. I'm looking for a way to be able to sort the range...
7
by: Bostonasian | last post by:
I have a table that contains transactional data. Such as site view by whom, when, which template, etc, etc... Everytime when I pulled the report, hh:mm:ss never matters. Only breakdown by dates,...
10
by: Willem | last post by:
Looking for some opinions on alternatives to programming with Access. I find that quite often I need to loop through my recordsets (first to last) performing calculations and was wondering if...
36
by: Ben Justice | last post by:
For a program in c, I need some random numbers for a system were people are placing bets. This is not a commerical project btw. Generally, I tend to rely on things from the standard library,...
8
by: hugh webster | last post by:
MySql seems to only accept dates as 'yyyy-mm-dd'. How do I do that when the user might input dd/mm/yy, or d.m.yyyy (I'm in Europe)? I know I can do sscanf, or explode to rebuild the date string -...
23
by: mike3 | last post by:
Hi. (posted to both newsgroups since I was not sure of which would be appropriate for this question or how specific to the given language it is. If one of them is inappropriate, just don't send...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.