Calculate prices

1. Junior Member
Join Date
Dec 1969
Posts
5

## Calculate prices

How can I calculate prices? Here&#039;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 = COST-DISCOUNT 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&#039;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&#039;t change all by themselves.

2. Senior Member
Join Date
Dec 1969
Posts
2,437

## Which DBMS?

Could you expand on the last question also?

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## 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>

4. Senior Member
Join Date
Dec 1969
Posts
1,843

## I like this

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

5. Junior Member
Join Date
Dec 1969
Posts
5

## RE: I think DB structure is wrong

That&#039;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 425-100<BR><BR>The equation to calculate the price changes depending on what the DISCOUNT_TYPE is. So....????

6. Junior Member
Join Date
Dec 1969
Posts
5

## RE: Which DBMS?

Microsoft Access, with ASP/VBScript

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## I *KNOW* that is not...

...what you *DID*. I&#039;m saying that&#039;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&#039;t like), then...<BR><BR>Try this *IN ACCESS* first, before trying it with ASP, okay?<BR><BR>SELECT IIF( [type]=&#039;\$&#039;, cost-discount_amount, cost*(1.0-discount_amount/100.0) ) AS price<BR>...<BR><BR>NOTE: That doesn&#039;t check to make sure TYPE is *only* either &#039;\$&#039; or &#039;%&#039;. It just assumes that if it isn&#039;t &#039;\$&#039; then it must be &#039;%&#039;. If you need to validate for only one of those two values, then say so. And say what to do if it isn&#039;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
•