Link to home
Start Free TrialLog in
Avatar of pigparent
pigparent

asked on

Rounded numbers are not rounded in dataset

I have a situation in which I have a charge for each person and a database of one or more records per person.  I need to allocate the charge on a percent-to-total basis to the rows in the dataset.  So I'm looping through each row in the dataset and making the calculation.  I want to store an amount rounded to the nearest penny on each row, so I'm making the calculation and when I step through it in debug mode it's perfect.  When I view the dataset at the end, there are fractions of pennies!  What on earth is causing this and how do I get it to just store the rounded amount that I am assigning?

All figures are of data type double and the field in the typed dataset is of type double.  As stated above, I have confirmed in debug that the custom function to round the numbers the way I want them rounded is working as designed.
Dim cmd As New OracleCommand("SELECT sum(v.gross_pay) AS GrossPay FROM wcs.wcs_uers_vouchers v LEFT OUTER JOIN hr.pay_cost_allocation_keyflex k ON v.cost_allocation_keyflex_id = k.cost_allocation_keyflex_id  WHERE v.social_security_number = '" & sSSN & "' AND k.segment3 IS NOT NULL AND (k.segment1 = '03' or k.segment1 = '07') AND v.check_date BETWEEN TO_DATE('" & dtStart & "', 'mm/dd/yy') AND TO_DATE('" & dtEnd & "', 'mm/dd/yy') AND frozen_for_adjustments IS NULL GROUP BY v.social_security_number", conOracle)
        Dim daOracle As OracleDataAdapter
        Dim dTotalFederalGross As Double = 0
        Dim dAmountToApply As Double = 0
        Dim dAmountApplied As Double = 0

        'Prepare
        Cursor.Current = Cursors.WaitCursor

        Try
            If conOracle.State = ConnectionState.Closed Then
                conOracle.Open()
            End If
        Catch orex As OracleException
            Cursor.Current = Cursors.Arrow
            MessageBox.Show("Oracle Production is down or not responding.  Please try again later.", Application.ProductName)
            Exit Sub
        End Try

        'Get total federal gross for this employee
        dTotalFederalGross = CDbl(cmd.ExecuteOracleScalar.ToString)

        'Populate the dataset of gross by PRC
        daOracle = New OracleDataAdapter("SELECT v.social_security_number AS SSN, '' AS EmpName, k.segment3 AS PRC, sum(v.gross_pay) AS GrossPay FROM wcs.wcs_uers_vouchers v LEFT OUTER JOIN hr.pay_cost_allocation_keyflex k ON v.cost_allocation_keyflex_id = k.cost_allocation_keyflex_id  WHERE v.social_security_number = '" & sSSN & "' AND k.segment3 IS NOT NULL AND (k.segment1 = '03' or k.segment1 = '07') AND v.check_date BETWEEN TO_DATE('" & dtStart & "', 'mm/dd/yy') AND TO_DATE('" & dtEnd & "', 'mm/dd/yy') AND frozen_for_adjustments IS NULL GROUP BY v.social_security_number, k.segment3 ORDER BY sum(v.gross_pay) DESC", conOracle)
        m_dsOracleFederallyFundedGrossByPRC = New dsOracleFederallyFundedGrossByPRC
        daOracle.Fill(m_dsOracleFederallyFundedGrossByPRC, "OracleFederallyFundedGrossByPRC")
        conOracle.Close()

        'Loop through the dataset and calculate the ESC charges by PRC
        For Each row As DataRow In m_dsOracleFederallyFundedGrossByPRC.Tables(0).Rows
            If row.Item("SSN").ToString = sSSN Then
                dAmountToApply = JasonsRound((CDbl(row.Item("GrossPay")) / dTotalFederalGross) * dESCCharges, 2)
                row.Item("AllocatedCharges") = dAmountToApply
                dAmountApplied += CDbl(row.Item("AllocatedCharges"))
            End If
        Next

        'Adjust for rounding error, if necessary
        If dAmountApplied <> dESCCharges Then
            For Each row As DataRow In m_dsOracleFederallyFundedGrossByPRC.Tables(0).Rows
                If row.Item("SSN").ToString = sSSN Then
                    row.Item("AllocatedCharges") = CDbl(row.Item("AllocatedCharges")) + dESCCharges - dAmountApplied
                End If
            Next
        End If

        Cursor.Current = Cursors.Arrow

Open in new window

Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

instead of double, use decimal data type
Are you sure that all the variables used in the operations are rounded?

For instance, we do not see where dESCCharges come from, but it is involved in the value that is stored in AllocatedCharges.

Also, I would be careful about things such as the following:

row.Item("AllocatedCharges") = dAmountToApply
dAmountApplied += CDbl(row.Item("AllocatedCharges"))

Open in new window


Why not

dAmountApplied += dAmountToApply

Open in new window


I would do all the operations with standard .NET types and wait till the operations are done before putting the information in the table. row.Item handles the data as Object. When you go in an out of a DataTable field, the system need to convert back and forth between an Object and a Double. That could bring little changes such as the ones you are getting.

Why not round when you make the last assignment to row.Item("AllocatedCharges").

Also, why design your own rounding function (JasonsRound, isn't it?) when there are a few built in Visual Basic and the framework Math class?

Avatar of pigparent
pigparent

ASKER

deighton, I am about to change to decimal to try it and will let you know.

JamesBurger, dESCCharges is an argument and was at four decimal places prior to your question.  I have since rounded it when the subprocedure is called and get the same result.

Thanks, too, for your suggesting dAmountApplied += dAmountToApply.  That's exactly the way I would have coded it but I didn't originally have dAmountToApply.  I added that solely to monitor the calculation to make sure that amount was rounded, and it is.  I will make that change you suggested.  I'm still perplexed about why the rounded number is being stored as something else, so I'll try deighton's suggestion.  I hate to do that because I always use double and don't see why it wouldn't work...but if the other works I may just switch to using decimal exclusively and ditch double.
Be careful about ditching Double for Decimal. This has a big impact.

Double are fixed to 15 decimals. Decimal has a variable number of decimals (their number depends on the integer part of the value) and can have up to 28 decimals. They do not solve rounding problems, they often make them worst.

A Decimal also takes up twice the amount of memory, 64 bits compared to the 32 bits of the Double.

Because of their extra size, Decimal variables are usually slower to use on a 32 bits computer.

Many programmers see Decimal as the type to use because many Math functions require a Decimal. It is simply because those functions should be able to handle any mathematical value, and Decimal is the type that can contains the biggest values as well as the most decimals.
JamesBurger (and deighton), I just tried this method with no change in the result.  I'm going to have to dig a little deeper on this one.  I honestly can't imagine why I can tell it to round and it just "decides" to have extra numbers in there...I won't call it "precision" since it is actually less precise in my case.

I have noticed that sometimes tables have double fields carried out several decimal places when I know for a fact that the entry would only have gone out two decimal places.  I have just shrugged and accepted it as some nuance of SQL Server but this time it's causing more annoyances.  I'll just round the final result and "stick" the pennies somewhere...

I'll leave this open another day to see if someone comes along and sees what the issue is.  I sincerely appreciate the assistance you both have provided!
ASKER CERTIFIED SOLUTION
Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Guys, thanks.  I guess I'll have to accept that, as much as it makes me scratch my head.  Good thing I work for the government and can say it's close enough for government work!