February 27, 2006 at 3:58 pm
Hi
I have two fields in my query - OwnContact and RevenueTotal
I need to return a third variable (Rate) based on the contents of these two fields. If OwnContact is a specific value (Y) then an overriding value occurs however if it is N then the value depends on the RevenueTotal.
In essence:
OwnContact = Y then Rate = 50
If OwnContact = Y, RevenueTotal < 40000 then Rate = 30
OwnContact = Y, RevenueTotal 40-50000 then Rate = 35
Else Rate = 40
I have tried a few ways of displaying this in the query but I am not sure of the most efficient method. Can anybody make any suggestions?
Thanks in advance for any assistance offered - this forum has been fantastic...
February 27, 2006 at 4:53 pm
select ..., CASE when OwnContact = 'Y' and RevenueTotal < 40000 then 30 when OwnContact = 'Y' and RevenueTotal between 40 and 50000 then 35 Else 40 as Rate, ...
_____________
Code for TallyGenerator
February 27, 2006 at 5:00 pm
Simon,
I'm not sure I understood your specs correctly, but a CASE statement is what you need to use.
SELECT
OwnContact
,
RevenueTotal
,
CASE
WHEN OwnContact = 'Y' THEN 50
WHEN RevenueTotal < 40000 THEN 30
WHEN RevenueTotal < 50000 THEN 35
ELSE 40
END AS 'Rate'
Each susequent WHEN is an implied ELSE of its own.
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply