Dynamic Deluxe!

I have a software system that needs to give the user to develop basically an
if statement. So IF PERSON_AGE > 15 AND MONTHLY_PURCHASE_AMT > 1000 THEN
GIVE THEM 1500 POINTS.
Here is the deal. I need to do it both during a back end process and
dynamically when a person comes to the counter. These marketing programs ar
e
definable by the instituion so we can't build the query ahead of time.
Anythoughts on a good way to do this. Keep in mind that there might be
multiple marketing programs active and the person qualifies for more then on
e
all should be able to display to the user. I would like to use a function so
that I can return a select statement with the messages to be presented to th
e
user.
Thanks for any thoughts.
Sammy DIt depends how complex your promotional formula needs to get but for
range-based criteria you could do something like this:
CREATE TABLE Promotions (promotion_code CHAR(10) PRIMARY KEY,
points_award INTEGER NOT NULL, age_min INTEGER NOT NULL, age_max
INTEGER NOT NULL, purchase_amt_min NUMERIC(10,2) NOT NULL,
purchase_amt_max NUMERIC(10,2) NOT NULL, ...)
Example. Promotions applicable to customer 1234:
SELECT P.promotion_code, P.points_award
FROM X, Promotions AS P
WHERE X.age BETWEEN P.age_min AND P.age_max
AND X.purchase_amt BETWEEN P.purchase_amt_min AND P.purchase_amt_max
AND customer = 1234
Where a particular attribute isn't relevant to the promotion just set
those values to be the min and max for the datatype.
David Portas
SQL Server MVP
--|||You might consider using a decision table program and not a database at
all.