SQL and ROUND()

  • Thread starter Thread starter Stephen
  • Start date Start date
S

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
 
Are you sure that you don't add a third non-zero argument to the Round
function?
 
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?
 
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)
 
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)
 
Giorgio!!! This was it!!
WOW!! This is so specific...I would have never figured this out.

Thank you.
 
Back
Top