T-SQL - rounding question

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
ryannAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This works on my SQL 2012 SSMS
SELECT 50000 / 50500.00 * 1499.98

Open 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.
0
Scott PletcherSenior DBACommented:
Change your original calc as follows:

,cast([E].OriginalCost * 1.0 / [TT].contractTotalOrigCost * [FE].GainLoss as decimal(9, 2)) as [GainLoss]
0
ryannAuthor Commented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
Oops, guess you are dealing with larger numbers than I thought:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ryannAuthor Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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 ...
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.
0
ryannAuthor Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
ryannAuthor Commented:
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]
0
ryannAuthor Commented:
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.
0
ryannAuthor Commented:
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
0
ryannAuthor Commented:
This formula does work THANKS! my error was being caused by another issue.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Wild curiosity overwhelms me ... did any of my comments help?
0
ryannAuthor Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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?
0
ryannAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.