Calculate Costs

[NotSoC#]

I have to fields in a table:

1. Age
2. FullCost

I would like a query to work out a discount cost to meet the following criteria:

if age > 12 and Fullcost is > 100
then DiscountCost = Fullcost * 0.5

Can't seem to get this to work in SQL

Anyone got any ideas...

Thanx in advance..............Chris

 

[dcx693]

You don't need to write the SQL. Just create a calculated column in the query. Use the IIf() function and nest one within another to test for multiple conditions. Something like:
=IIf([Age]>12,IIf([FullCost]>100,[FullCost]*.5,0),0)
That formula either evaluates to [FullCost]*.5 if both conditions are true, or to 0 otherwise.

 

[FoFa]

SELECT AGE, IIF([AGE]>12 AND [FullCost]>100), [FullCost] * .05,[FullCost]) as FCost From MyTable

OR
SELECT AGE, FullCost from Mytable where Age < 13 OR FullCost < 101 UNION ALL SELECT AGE, FullCost * .05 from Mytable where AGE>12 AND FullCost > 100

Just off the top of my head

 

[NotSoC#]

First class...

Thank you very much...

Chris

 

[dcx693]

Very cool. I don't know why I never tried to string criteria along with "And" inside an IIF() function before!