How can I calculate prices? Here’s what I have: a table named tblProducts with fields including COST, DISCOUNT, and DISCOUNT TYPE. The problem is, some products are discounted by a dollar amount and others by a percent, like this:
COST DISCOUNT DISCOUNT_TYPE
300 50 %
250 30 $
300 50 $
I need to display the calculated PRICES on a web page. But the math equation changes if the DISCOUNT TYPE is $ or %. How do I construct the SQL to createa VIEW where I have “IFs”. Like, IF DISCOUNT_TYPE=$ THEN PRICE = COST-DISCOUNT ELSE IF DISCOUNT_TYPE=% THEN PRICE = (COST-(COST*DISCOUNT/100))
I need to “link” the prices to the products (in tblProducts) and I need the web page to be able to SORTby PRICE. I have done this by storing the PRICES in the table itself, but I know that’s poor design. Do I create a View? Stored Procedure?
Other problem is when I “globally” update the DISCOUNT (all products starting with “a” set the DISCOUNT to 35%). The change needs to be reflected in the PRICES and when the prices are stored in the table, they don’t change all by themselves.
