T-SQL - rounding question

This works on my SQL 2012 SSMS

SELECT 50000 / 50500.00 * 1499.98

Select allOpen in new window

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.

Change your original calc as follows:

,cast([E].OriginalCost * 1.0 / [TT].contractTotalOrigCost * [FE].GainLoss as decimal(9, 2)) as [GainLoss]

These are money types.

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.

Getting the same error with the new formula.

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

>These are money types.
>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 ...

Hi Jim. In my last post I uploaded a file which shows the data types of each field.
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.

I don't see any file uploaded to this question.     Also, using the code block instead of the file upload would save us a bunch of clicks.

Also, keep in mind that experts here do not have access to your data source, so we can't do a 'grip it and rip it' and execute code, then troubleshoot from there.    So there are limitations to the 'here's my big pile of T-SQL, please find the error' approach.

Field                  DataType      Length            Numeric Precision      Numeric Scale
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]

I pulled out the section of the stored proc that has the formula in it and it ran ok. I guess I don't know how to debug this.

the error gives the line number when I execute the stored proc.  I will take Jim's advice and go see what line that is in my stored proc

This formula does work THANKS! my error was being caused by another issue.

Wild curiosity overwhelms me ... did any of my comments help?

Jim not for the original question. I had a co-worker show me how to debug the sproc to find out what else was going on.

Very well, but please consider that your original question was missing a lot of necessary information to answer your question, which forces experts to spend a lot of time asking multiple exploratory questions to get to the root cause of the problem, which wasn't awarded with points.  This will lead experienced experts to intentionally avoid your questions in the future.

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?

sure. The formula given to me when run outside my stored proc ran correctly and gave me the correct numbers. What was throwing me was the error,  which I did not get when running it with my old formula. This stored proc uses 2 unions. First time I've done one. I was moving spaces to some of the columns in the union when they were integer fields.