SQL and ROUND()

[Stephen]

I have an ADP runnning on SQL2000 and this calculation is driving me crazy.

I have a simple calculation that determine commissions.

The base amount is a value of: $12,736.50
The agent's portion is 85%

SQL is using the formla as follows:

Commission = (PostMLS - PostMLS * PercentPromo * TCFEMultiplier) *
PercentCommissionSplit
POSTMLS = $12,736.50
PercentPromo = 0
TCFEMultiplier = 1
PercentCommissionSplit = .85

This SHOULD give me a value of $10,826.025 (which it does)
However, in order to divide up the actual dollars, I need to round this
value and give the remainder to the office

Hence, $10,826.025 SHOULD be rounded to $10,826.03 and the office should get
$1,910.47

Therefore, I used the equation: ROUND((PostMLS - PostMLS * PercentPromo *
TCFEMultiplier) * PercentCommissionSplit,2)

This is the crazy part: this formula keeps returning $10,826.02

Why is it NOT rounding UP?

Help?

-Stephen

 

[Sylvain Lafontaine]

Are you sure that you don't add a third non-zero argument to the Round
function?

 

[Stephen]

I'm not sure what you mean. Please explain.

Are you sure that you don't add a third non-zero argument to the Round
function?

 

[Sylvain Lafontaine]

If you add a third non-zero argument to the Round function, it will truncate
instead of rounding.

I tried your exemple (ROUND(12736.50 * 0.85, 2)) in SQL-Server Query
Analyser (QA) and it gives me the right answer (10826.03). Maybe it's some
problem with the underlying type of the variables or fields. Have you tried
whit the QA?

 

[giorgio rancati]

Hi Stephen,

If you use a float datatype the result is approximate.

----
Declare @POSTMLS Float,
@PercentPromo Float,
@TCFEMultiplier Float,
@PercentCommissionSplit Float


Select @POSTMLS = 12736.50,
@PercentPromo = 0,
@TCFEMultiplier = 1,
@PercentCommissionSplit = .85

Print 'Float result'
Select Round((@PostMLS - @PostMLS * @PercentPromo * @TCFEMultiplier) *
@PercentCommissionSplit,2)

Print 'Numeric result'
Select Round(Convert(Numeric(18,4),(@PostMLS - @PostMLS * @PercentPromo *
@TCFEMultiplier) * @PercentCommissionSplit),2)

 

[david epsom dot com dot au]

In Access immediate window:

?Round(10826.025)
10826.02

It looks like you are getting VB/Windows rounding rather than SQL Server
rounding.

VBA uses a Windows DLL, which, when it works correctly, rounds towards even
numbers. (Bankers Rounding)

(david)

 

[Stephen]

Giorgio!!! This was it!!
WOW!! This is so specific...I would have never figured this out.

Thank you.