SQL perform calculation to derive column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL perform calculation to derive column

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-08-05 : 07:41:10

I have the following query

SELECT f.supplier_name,d.product_ID_key, d.product_name AS productName, d.packsize, d.tradePrice, d.IPU_code, d.EAN_code, c.discount, e.stock_indicator
FROM aw_customer AS a INNER JOIN
aw_cust_contract AS b ON a.cust_ID_key = b.cust_ID_key INNER JOIN
aw_contract_line AS c ON b.contract_ID_key = c.contract_ID_key INNER JOIN
aw_product AS d ON c.product_ID_key = d.product_ID_key INNER JOIN
aw_stock AS e ON d.product_ID_key = e.product_ID_key INNER JOIN
aw_supplier AS f ON d.supplier_ID_key = f.supplier_ID_key
WHERE (a.cust_ID_key = @customerId)
ORDER BY d.product_name

And I would like the add a column called Actual_Price which is
d.tradePrice - c.discount
where tradePrice = money type
and discount is a decimal

Any ideas how I can do this ?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-05 : 09:27:13

You should be able to just add:

SELECT f.supplier_name,d.product_ID_key, d.product_name AS productName, d.packsize, d.tradePrice, d.IPU_code, d.EAN_code, c.discount, e.stock_indicator,
(D.TRADEPRICE-CONVERT(MONEY, C.DISCOUNT)) AS ACTUAL_PRICE
FROM aw_customer AS a INNER JOIN
aw_cust_contract AS b ON a.cust_ID_key = b.cust_ID_key INNER JOIN
aw_contract_line AS c ON b.contract_ID_key = c.contract_ID_key INNER JOIN
aw_product AS d ON c.product_ID_key = d.product_ID_key INNER JOIN
aw_stock AS e ON d.product_ID_key = e.product_ID_key INNER JOIN
aw_supplier AS f ON d.supplier_ID_key = f.supplier_ID_key
WHERE (a.cust_ID_key = @customerId)
ORDER BY d.product_name

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-08-05 : 09:36:58

looks good just the maths part has to be slightly different

i.e how do I do 18.0000 - 8 where 8 is a % really

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-05 : 11:19:47

Could you be more specific? What is 8 a percentage of? Can you post the math (not the query) that you're trying to accomplish? Like

18.0000 - x% of Y etc

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-08-05 : 11:23:10

So we have the Total Price which is i.e.

18.0000

Then I have the discount decimal i.e
8

So the column I am trying to calculate in sql is

18.0000 - ((18.000 / 100) * 8)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-05 : 12:50:52

So instead of (D.TRADEPRICE-CONVERT(MONEY, C.DISCOUNT)) AS ACTUAL_PRICE, you'd write something like this:

(D.tradeprice - (D.tradeprice/100) * CONVERT(MONEY, C.DISCOUNT)) AS ACTUAL_PRICE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources