This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I need the result of this calculation to be = to 1,485.13

My result is 1,484.98. How do I get it to round up?

My formula:

,round([E].OriginalCost / [TT].contractTotalOrigCost * [FE].GainLoss,2) as [GainLoss]

[E].OriginalCost = 50,000,00

[TT].ContractTotalOrigCost = 50,500.00

[FE].GainLoss = 1,499.98

50,000 / 50,500 * 1,499.98 = 1,484.98 Should be 1,485.13

My result is 1,484.98. How do I get it to round up?

My formula:

,round([E].OriginalCost / [TT].contractTotalOrigCost

[E].OriginalCost = 50,000,00

[TT].ContractTotalOrigCost

[FE].GainLoss = 1,499.98

50,000 / 50,500 * 1,499.98 = 1,484.98 Should be 1,485.13

```
SELECT 50000 / 50500.00 * 1499.98
```

What are the data types of these three values?

This can alter the result based on the scale (number of decimal places allowed) in each member of the expression. This is loosely referred to as 'Integer Math', where dividing the integer values 9 and 4 results in the integer value 2, instead of the decimal value 2.25.

Note that in the above expression all I changed was adding .00 to the middle value, which forces both expressions to calculate with two decimal places.

,

Scott when I ran it with your formula I get this error:

Msg 8114, Level 16, State 5, Procedure LtiRptTaxGainLossSP, Line 70

Error converting data type varchar to numeric.

I am a relative newbie to SQL. Don't know what to look for here.

I've attached the properties of the OriginalCost , GainLoss and Quantity

ContractTotalOrigCost is calculated in a temp table using this formula:

,SUM ([E].OriginalCost * [E].quantity) as [contractTotalOrigCost]

I Tried changing it to 19,4 and got the same error

>Error converting data type varchar to numeric.

Both of the above are mutually exclusive, unless the line 70 where the error occurs is a different line than the expression in this question.

So, do us a heavy and ...

Double-check the data types, and post them here.

Execute the function, and when it errors again double-click on that error message, and note where the cursor jumps. That is the line that caused the error.

Copy-paste your script into a code block in this question, highlighting the line where the cursor jumped (i.e. line 70, which will be different then line 70 in the code block.

It is the formula causing the issue. When I go back to the way I was doing it before it runs ok.

The code I am executing is a stored proc.

OriginalCost money 8 19 4

GainLoss money 8 19 4

Quantity int 4 10 0

ContractTotalOrigCost is summed in a temp table using this formula:

,SUM ([E].OriginalCost * [E].quantity) as [contractTotalOrigCost]

And we still don't know the answer to the question as 'I had a co-worker show me how to debug doesn't tell us an answer, so now experts-exchange has a question without an answer, which isn't going to help future members solve their own problems. Any chance you can spell out for us what the answer was?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

,cast([E].OriginalCost * 1.0 / [TT].contractTotalOrigCost

19, 2)) as [GainLoss]