
Calculate prices
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:<BR>COST DISCOUNT DISCOUNT_TYPE<BR>300 50 %<BR>250 30 $<BR>300 50 $<BR><BR>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 create a VIEW where I have "IFs". Like, IF DISCOUNT_TYPE=$ THEN PRICE = COSTDISCOUNT ELSE IF DISCOUNT_TYPE=% THEN PRICE = (COST(COST*DISCOUNT/100))<BR><BR>I need to "link" the prices to the products (in tblProducts) and I need the web page to be able to SORT by 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?<BR><BR>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.

Which DBMS?
Could you expand on the last question also?

I think DB structure is wrong
If you did *THIS*:<BR><BR>COST DISCOUNT_AMT DISCOUNT_PCT<BR>300 0 50<BR>250 30 0<BR>300 50 0<BR>400 0 20<BR><BR>Then no IF style test is needed!<BR><BR>SELECT ( ( COST  DISCOUNT_AMT ) * ( 1  DISCOUNT_PCT/100) ) AS price<BR><BR>Presto.<BR><BR>

I like this
because it also give you the ability to apply a dollar and percentage discount to a given product.

RE: I think DB structure is wrong
That's not what I did. 3 columns are COST, DISCOUNT_AMOUNT, and DISCOUNT_TYPE. The TYPE specifies either $ or %, so the equation changes each time. Like:<BR>cost=500, discount=25, type=%, so 500(500*25/100)<BR>cost=425, discount=100, type=$, so 425100<BR><BR>The equation to calculate the price changes depending on what the DISCOUNT_TYPE is. So....????

RE: Which DBMS?
Microsoft Access, with ASP/VBScript

I *KNOW* that is not...
...what you *DID*. I'm saying that's what you SHOULD have done.<BR><BR>But since you have *NOW* told us which DB...<BR><BR>If you still want to use your version (which I still don't like), then...<BR><BR>Try this *IN ACCESS* first, before trying it with ASP, okay?<BR><BR>SELECT IIF( [type]='$', costdiscount_amount, cost*(1.0discount_amount/100.0) ) AS price<BR>...<BR><BR>NOTE: That doesn't check to make sure TYPE is *only* either '$' or '%'. It just assumes that if it isn't '$' then it must be '%'. If you need to validate for only one of those two values, then say so. And say what to do if it isn't either one.<BR><BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

