Another thing to note is that while Dr. X's shift ENDS at 8AM 12/5/03, DR. X's BEGINS at 8AM 12/5/03.
:)
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/3/2003 12:51:27 PM >>>
To answer your first question, ok, my brain isn't working properly yet. I
was including them because I was just automatically thinking that the shift
would end at the end of the day not the beginning and therefore if someone
chose that day to start there would be an overlap. Using times in the test
would have worked with the > and <. I stand corrected again.
As for the second question. I really need to get over the flu before I hit
reply. You are right. I completely forgot to deal with date ranges that
are larger than my select range.
Now I am going to go take some Dayquil and admit defeat.
Steve
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 10:08 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> BEGIN
>
> declare @yourTable table(
> EntryNo int,
> startDateField datetime,
> endDateField datetime
> )
>
> insert into @yourTable(entryNo, startDateField, endDateField) values(1,
'11-25-2003', '12-15-2003')
> insert into @yourTable(entryNo, startDateField, endDateField) values(2,
'12-31-2003', '01-15-2004')
> insert into @yourTable(entryNo, startDateField, endDateField) values(3,
'11-25-2003', '12-01-2003')
> insert into @yourTable(entryNo, startDateField, endDateField) values(4,
'1-25-2004', '2-15-2004')
> insert into @yourTable(entryNo, startDateField, endDateField) values(5,
'12-25-2003', '12-30-2003')
>
> select *
> from @yourTable
> where startDateField < '12-31-2003'
> and endDateField > '12-01-2003'
>
> delete from @yourTable
>
> end
>
> This worked but missed entries 2 and 3, mine picked them up immediately.
It is supposed to miss them. If a persons shift ends at X, why
shouldn't the next shift of that person be allowed to start at X
either?
> I will say this though, for Candace's problem your solution with the added
=
> is probably better.
Even without the added =. Your solution still does not find any
that completely envelope other ranges. PostgreSQL shows:
jochemd=> BEGIN;
jochemd=> CREATE TABLE yourTable (
jochemd(> startDateField TIMESTAMP,
jochemd(> endDateField TIMESTAMP);
jochemd=> INSERT INTO yourTable
jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59');
jochemd=>
jochemd=>
jochemd=> SELECT *
jochemd-> FROM yourtable
jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59')
jochemd(> OR
jochemd(> (endDateField between '2003-12-01 00:00:00'
and '2003-12-31 23:59:59'));
startdatefield | enddatefield
----------------+--------------
(0 rows)
jochemd=>
jochemd=> ROLLBACK;
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
