What about something like
SELECT
*
FROM
[table]
WHERE
date BETWEEEN
ISNULL( Effective_Date, 0 ) AND
ISNULL( Deactive_Date, getDate() )
The first ISNULL uses the zero date (in think usually 1900-1-1) if the date
is null. The Second ISNULL uses the current date if null.
.......................
Ben Nadel
www.bennadel.com
-----Original Message-----
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 03, 2006 4:26 PM
To: CF-Talk
Subject: SQL Select date between two dates with possible NULL values.
I have a table with data like this.
Account_Manager Goal Effective_Date Deactive_Date
Joe 750 1/1/2005 5/31/2005
Joe 825 6/1/2005 8/31/2005
Joe 900 9/1/2005 NULL
Sam 700 7/15/2005 8/31/2005
Sam 775 9/1/2005 NULL
I need a query that will tell me each account manager's goal for a given
date, say 8/1/2005. How would one do that, with the null? My first thought
was WHERE date > Effective_Date AND date < Deactive_Date, but that would not
work with the null values would it?
--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
---------
| 1 | |
--------- Binary Soduko
| | |
---------
"C code. C code run. Run code run. Please!"
- Cynthia Dunning
Confidentiality Notice: This message including any attachments is for the
sole use of the intended
recipient(s) and may contain confidential and privileged information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender and delete any
copies of this message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248794
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
