Rounding Question

[Deam]

My select statement:

SELECT RIGHT('00000000'+ CONVERT(varchar(8),CONVERT(numeric(8,4),(21630 * 0.00120192))),8)

gives the result of 025.9975

How do I make it round up so that I get 26.00 ?

 

[SQLDenis]

SELECT round(RIGHT('00000000'+ CONVERT(varchar(8),CONVERT(numeric(8,4),(21630 * 0.00120192))),8),2)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Now I know why formatting in SQL sux

Try this:

SELECT RIGHT('00000000'+ CONVERT(varchar(8),ROUND(CONVERT(numeric(8,4),(21630 * 0.00120192)), 0)),8)


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Deam]

I should have been more clear in my initial question...I apologize...

my situation is that i an selecting the salaries of some employees. When I select the yearly salary of an employee, based on the # of hours(that is the .0012 number you see) they put in I have to convert it to their hourly rate.
Example 1:
So someone gets paid 47000 per year when I convert their salary, I should get 22.6000. But when I use my select statement it converts it to 22.5961...which is incorrect in payroll. For this case the select statement I used is:
SELECT RIGHT('00000000'+ CONVERT(varchar(8),ROUND(CONVERT(numeric(8,4),(47000 * 0.000480769)), 0)),8)
Basically I need it to round up the fourth decimal based on the fifth decimal...

Example 2:
Someone gets paid 21630 per year when I convert their salary, I should get 26.0000. But when I use my select statement it converts it to 25.9975...which is incorrect in payroll. For this case the select statement I used is:
SELECT RIGHT('00000000'+ CONVERT(varchar(8),ROUND(CONVERT(numeric(8,4),(21630 * 0.00120192)), 0)),8)
Basically I need it to round up the fourth decimal based on the fifth decimal...

Hope I am making sense here

 

[vongrunt]

Problem happens because finite-precision number is used to represent rational number with infinite number of decimals. This is basically the same as buying 3 items for $100. One item costs $33.33. Multiplied back by 3 this gets $99.99.... and 1 cent went bye-bye.

That said, 0.00120192 is waaay inaccurate. And there are no guarantees rounding up the 4th decimal will always produce results you want.

One of mathematical recommendation is to postpone any division (/ operator) as much as possible; in this case hourly rate is result of division, right?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Deam]

Yes you are correct...and I am stumped as to what I should do...The auditors are coming in next week and I need this reolved before then !!

 

[vongrunt]

One choice: calculate houry rates more accurately (and use more decimals if necessary) and then use ROUND(value, 3) to round up the 4th decimal. For example:

-- this is with existing rate
SELECT ROUND(21630 * 0.00120192, 3)
-- this is with more accurate rate
SELECT ROUND(21630 * 0.00120203, 3)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)