Click here to Skip to main content
15,075,944 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have written code that returns a total discouned price. The figures in the result set are correct, but they are returned as a negative figure.
Any ideas on how prevent this

SQL
SELECT Company.CompName AS [Company Name], Products.Prodname AS [Product Name], Orders.ProdQty As Quantity,
Orders.ProdQty * Products.Cost As [Total Cost], (Orders.Discount / 100) * (Orders.ProdQty * Products.Cost) - (Orders.ProdQty * Products.Cost) AS [Discounted Cost]
From Orders
INNER JOIN  Company  ON Orders.CompanyID = Company.CompanyID
INNER Join Products ON Orders.ProdID = Products.ProdCode
ORDER BY Company.CompName


Reults as below:
Acme Ltd Christmas Box 300 5100 -4335
Test Company Ltd Christmas Cracker Hamper 530 34450 -31005
Test Company Ltd Christmas Cracker Carton 23 1265 -1106.875
Test Company Ltd Christmas Box 20 340 -297.5

Thanks
Posted

Try this (it looks like you forgot to scope out part of the math):

SQL
((Orders.Discount / 100) * (Orders.ProdQty * Products.Cost)) - (Orders.ProdQty * Products.Cost)
   
Hi Thanks for you reply.

This still returns a negative. All of the fields used in the calculation are of datatype float, if this makes a difference.
Thanks
   
Shouldn't you simply have it other way round (normal price - discount amount):
SQL
(Orders.ProdQty * Products.Cost) - ((Orders.Discount / 100) * (Orders.ProdQty * Products.Cost))  AS [Discounted Cost]

or simply:
SQL
(Orders.ProdQty * Products.Cost) * (1- (Orders.Discount / 100))
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900