Decimal field SUM rounding up!

bananas · May 14th, 2007, 04:57 AM

I'm using the following~

SELECT @TotalCost = Sum(ItemPrice)
FROM dbo.Orders
WHERE UserID = @UserId

My ItemPrice Column and @TotalCost variable is Decimal(6,2).
But when i call the @TotalCost variable it has been rounded up!

How do i get 2 decimal places on my @TotalCost SUM variable?

happygv · May 14th, 2007, 05:37 AM

It can't be... I think you are missing something there.
Can you post your complete code... that has the Variable declaration too? Also generate the CREATE TABLE script for that table and post it here.

_________________________
- Vijay G
Strive for Perfection

bananas · May 14th, 2007, 06:11 AM

hi happygv,

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[getOrderData]

@ApplicationName        nvarchar(256),
@UserName                nvarchar(256),
@TotalItemPoints        smallint OUTPUT,
@TotalCost                decimal(6,2) OUTPUT

as 

BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications 
            WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT        ItemID, ItemQTY, ItemPrice, ItemPoints, 
                UserID, OrderDate, CartDesc
    FROM        dbo.Orders INNER JOIN dbo.Products ON 
                dbo.Orders.ItemID = dbo.Products.PID
    WHERE       UserID = @UserId

-- Find Total Points & Cost
    SELECT @TotalItemPoints = Sum(ItemPoints), @TotalCost = Sum(ItemPrice)
    FROM dbo.Orders
    WHERE UserID = @UserId
    IF @TotalItemPoints is NULL
    SET @TotalItemPoints = 0    
--

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate= GetDate()
        WHERE  UserId = @UserId
    END
END

------------------------------------------------------------
Not sure how to generate a create table script but I should tell you I am pulling the data up using ASP.net by the following code:

Code:

Sub CartInserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) Handles Cart_DS.Inserted
                TotalPoints_lbl.Text = "Points: " & e.Command.Parameters("@TotalItemPoints").Value
        TotalCost_lbl.Text = "Total Cost: " & String.Format("{0:N}", Math.Round(CDec(e.Command.Parameters("@TotalCost").Value), 2))

    End Sub

Code:

<InsertParameters>
                            <asp:Parameter Name="TotalCost" Type="Decimal" Direction="Output"
                            ConvertEmptyStringToNull="true" DefaultValue="0.00" />

                        </InsertParameters>

happygv · May 14th, 2007, 07:19 AM

You are doing a

Code:

Math.Round(CDec(e.Command.Parameters("@TotalCost").Value),

Is that the culprit?

Moreover should you not use a FIXED POINT rather than using a NUMBER?
String.Format("{0:N}",
replaced with...
String.Format("{1:F}",

I think one of these or combination of these are killing your value.

_________________________
- Vijay G
Strive for Perfection

bananas · May 14th, 2007, 07:49 AM

Thanks for the suggestions, the format and rounding I applied to the result was a bit of a red herring as without any formatting it still produced a rounded up figure. I left the code in as it seemed the problem lay elsewhere - I was thinking the SQL. Anyway saying that I tried your ideas - removed the "math.round" and changed to "string.format{1:f}" and they still yield a rounded up figure.
I'm stumped!

bananas · May 14th, 2007, 07:59 AM

I'll just give the results of the changes:
If:

ItemPrice1 = Â£5.41
ItemPrice2 = Â£1.10
Actual SUM value should be 6.51, but..
results:
e.Command.Parameters("@TotalCost").Value = 7
String.Format("{0:N}", Math.Round(CDec(e.Command.Parameters("@TotalCost") .Value), 2)) = 7.00
String.Format("{0:F}", CDec(e.Command.Parameters("@TotalCost").Value)) = 7.00
String.Format("{0:F}", e.Command.Parameters("@TotalCost").Value) = 7.00

happygv · May 14th, 2007, 08:13 AM

I dont suspect it to be issue with SQL. Since I created a new table with COL1 as decimal(6,2)

1) Did a Select Sum(COL1) from Table1. that resulted only in decimal value.
2) DECLARE @totalcost DECIMAL(6,2)
SELECT @totalcost = Sum(COL1) from Table1
SELECT @totalcost
resulted in decimal value. So thought it could be something to do with your frontend code.
See if you are missing something else that we haven't seen yet from the frontend.

Also see if this helps.
String.Format method

cheers

_________________________
- Vijay G
Strive for Perfection

bananas · May 14th, 2007, 09:37 AM

I've been shaking my code about but I can't solve the problem!
I've tried various String Formats; altered the code that interacts with the SQL; tried various controls to display the data- nothing!
When I use a datagrid to show the ItemPrices it displays them correctly with decimal places, so they are OK.
I'm thinking I could use a datatype of Double instead of Decimal as maybe my ASP.net coding would prefer that!
I'm going ot try different data types.

bananas · May 14th, 2007, 09:46 AM

OK i've finally got it!

I changed the ItemPrice DB Type and @ToalItemPrice to 'Money', and in my front end code I set the @TotalItemPrice as a 'Double'.
I then apply String.Format{0:N} to give a consistent 2 Decimal places.
({1:F} doesn't work for me)

thanks for your help happygv ;)

happygv · May 14th, 2007, 10:08 AM

Hmmmm! that is good to know. It didn't strike my mind to ask what datatype you have been using in front end. That could have saved sometime.

cheers

_________________________
- Vijay G
Strive for Perfection