Yet another computed column problem

ize_man
ize_man used Ask the Experts™
on
Hi
Once again I am trying to get it right. Computed column.... I "stole" this one from Adventure Works db but i am not getting the right values.

I have a col UnitPrice (money) , UnitPriceDiscount (money) witch is suppose to contain the discount percentage and Quantity (int) then I have a col called LineTotal (the computed one). I have tried this:
(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[Quantity],(0.0)))

but I am not getting the right values.

I am using RedGate data generator to insert 5,000,000 rows into my db for testing and the values are a mess...
If I enter the data manually and enter UnitPrice=1,00 Quantity=1 and UnitPriceDiscount=0,1 I get the "correct" value with to many decimals value = 0,900000.

What I would like to do is that UnitPriceDiscount should be between 0-100 (%) and the value should be 0,90 ...I believe the "scale" value for the computed col has to do woth the number of decimals..how do I change that??

Thankful for your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>UnitPriceDiscount (money) witch is suppose to contain the discount percentage
a discount percentage field should NOT be money, but decimal.

>UnitPrice (money)
honestly, I don't like to use the money as data type in a table, unless for import/export in a staging table.
the currency should really be in a distinct column, and not be mixed with the numerical amount.

>I have tried this:
>(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[Quantity],(0.0)))
the problem is the quantity which is int. you should make that column also a decimal (20,4), and the problem is solved implicitly.

if you don't want to do that:
(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*cast([Quantity] as decimal(20,4)),(0.0)))

Author

Commented:
Hi

Ok, now I have changed UnitPriceDiscount to decimal(18,2), Quantity to decimal(20,1) and UnitPrice to int and then I use this formula:
(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[Quantity],(0.0)))
and the result is -9,000 (why only 3 decimals now?? Is it because of the original values (20,1) and (18,2)??) and there is a change in UnitPriceDiscount from 0,1 to 10,00
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>UnitPrice to int
huh? decimal(18,2) should be more appropriate...

anyhow, the result you get comes from the int. once "int" is in an expression, everything get's rounded to int...
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Ok..now I have changed UnitPrice to decimal(18,2) but the value is still -9,00000 (with 5 decimals now :-( )
Chris LuttrellSenior Database Architect

Commented:
try this for your computed column formula, it took the number of decimals back down to the same as the Money type in Unit Price for me:
(CONVERT([money],isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[Quantity],(0.0)),0))
Chris LuttrellSenior Database Architect

Commented:
oh, and I was using your original datatypes from the original question.

Author

Commented:
Ok. I will try that out..but it will (as far as I can see) still result in the same value -9,00 ...that's the biggest problem. I will test it

Author

Commented:
yes..it reduced the decimals to 2 :-) ...but the value is still -9,00 when Quantity = 1, UnitPrice = 1 and UnitDiscount=10 but when I set the UnitDiscount to 0,1 the result is 0,90...the correct one. But how do I get to set UnitDiscount to 10 (like 10%)??
Senior Database Architect
Commented:
sorry, I missed that part.  If you are storing 10% as 10 in that column then this fomula will calculate the value of +9,00 for your 1, 1, 10 test case.  I was storing it as .10 the first time.
CONVERT(MONEY,ISNULL([UnitPrice]*((1.0)-ISNULL([UnitPriceDiscount]/100,0.0))*[Quantity],0.0))

Author

Commented:
That's it :-) works perfectly. Thank you :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial