On 8/3/06, Ian Skinner <[EMAIL PROTECTED]> wrote: > 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? >
You could use COALESCE and some date in the future... WHERE date > Effective_Date AND date < COALESCE(Deactive_Date,'1/1/3000') or WHERE date > Effective_Date AND date <= COALESCE(Deactive_Date,date) -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:248772 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
