Percentage calculated field

Question

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 17-Aug-11 2:49am

Member 7940373

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

#realJSOP · Answer 1 · 2011-08-17T02:54:00

Solution 1

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)

Posted 17-Aug-11 2:54am

#realJSOP

Member 7940373 · Answer 2 · 2011-08-17T03:01:00

Solution 2

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

Posted 17-Aug-11 3:01am

Member 7940373

Wendelius · Answer 3 · 2011-08-17T07:53:00

Solution 3

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

Posted 17-Aug-11 7:53am

Wendelius