# How do I "Turn off" the Ceiling function in SQL Server

This is from a SQL Server StoredProcedure
I am using the ceiling function to round off @X to the next highest whole number. Example 1.95, rounds off to 2. When I multiple 10.95 by the value of @X the product is rounding off to the next highest also. Can I stop this?  Example: If @x = 2 ATotalweight should be 21.90 but it is rounding to 22. I want 21.90

declare @x  decimal

@X=sum(ceiling(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20),
@Totalweight= 10.95*@X
###### Who is Participating?

Commented:
PAQed, with points refunded (0)

GhostMod
Community Support Moderator
0

Commented:
I think the / 20 is in the wrong place:

@X=sum(ceiling(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity/20))
0

Author Commented:
Nope that divides CMRC_ShoppingCart.Quantity
0

Commented:
what are the types of your columns and of @Totalweight ?

select sum(ceiling(1.91 * 2)/20) * 10.95

returns 2.19 as it should ...
0

Commented:
did you really intend to get the sum of all the ceilings?  Seems like you'd be introducing a lot of reounding error, if so, how about

declare @X  decimal
declare @Y  decimal

@X=sum(ceiling(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20),
@Y=sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity/20),
@Totalweight= 10.95*@Y

or

@X=sum(ceiling(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20),
@Totalweight= 10.95*sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20,

0

Commented:
You haven't specified the scale of the decimal so it's defaulting to 0 decimal places

try something like this:

declare @x  decimal(10,2)

0

Author Commented:
Unfortunatly none of these work,
@Totalweight= 10.95*sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20 is returning 13 for the weight values I have entered on the web page. It should be returning 10.95.

The purpose of this procedure is to charge \$10.95 for each 1-20lbs of shipping weight. another words, if the total shipping weight is 19lbs the charge will be \$10.95. If the weight is 20.5ibs the charge will be \$21.90.

I can do it right on the .aspx page, but this creates other problems down the line.  I am rethinking the whole process.
0

Author Commented:
declare @x  decimal(10,2) is returning 13 where it should be 10.95 too. thanks!
0

Commented:
as I said what are the types of your other variables since

select sum(ceiling(1.91 * 2)/20) * 10.95

gives the behavior you expect
0

Author Commented:
Sorry I missed this one!

CMRC_Products.weight2 decimal (18,2)
CMRC_ShoppingCart.Quantity  int
@totalweight money OUTPUT

0

Commented:
Oh, you have sum and ceiling backwards in your first example
For this table

Order      Weight     Quantity
1      1.50      5
1      2.60      2
2      3.50      6
2      4.20      4

You get the ship cost with "10.95*ceiling(sum(weight * Quantity)/20)" not "10.95*sum(ceiling(weight * Quantity)/20)" as you had.  Look, for example at this query,

select ordernum,sum(weight * Quantity) 'Total Weight',sum(weight * Quantity)/20 'Weight/20', ceiling(sum(weight * Quantity)/20) '20 Weights',10.95*ceiling(sum(weight * Quantity)/20) 'Ship Cost' from mathtest
group by orderNum

Which returns
ordernum     Total Weight      Weight/20            20 Weights      Ship Cost
1         12.70            .635000            1            10.95
2         37.80            1.890000            2            21.90

0

Commented:
OK, I guess tabs don't work well, here it is space out.

ordernum     Total Weight     Weight/20          20 Weights     Ship Cost
1                  12.70                .635000                  1             10.95
2                  37.80               1.890000                 2             21.90
0

Commented:
e.g. you don't want the sum of all the ceilings, you want the ceiling of the (sum/20)
0

Author Commented:
Ok, I'm still getting a rounded of amount.
the amount I am getting on the current test order is 11.00 for a 15lb order. it should be 10.95.
I have something wrong somewhere

Here is what I have:
@Totalweight=10.95*ceiling(sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20)

on my aspx page:
Protected totalweight as decimal
lblshiptotAL.TEXT = String.Format("{0:c}", totalweight)

totalweight gets the value from @totalweight in the procedure
0

Commented:
Is @Totalweight a "decimal (10,2)" or just "decimal"?  For example

select convert(decimal,10.95), convert(decimal(10,2),10.95)
returns

11      10.95
0

Author Commented:
In the stored procedure it is money

@Totalweight Money
0

Author Commented:
i now have

@Totalweight=convert(decimal(10,2),10.95)*ceiling(sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20)

still get \$11.00
0

Commented:
If you make @TotalWeight decimal(10,2) does it change?  What is you simply query for
10.95*ceiling(sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20)?
0

Commented:
You said it was just "decimal" in ASPX.  Maybe it's the pass back to ASPX that converts it to decimal(x,0)?  When you get \$11, is it in the ASPX, or are you using something like SQL Query Analyzer to test it?
0

Author Commented:
It has to be something like that. Got to do lunch, then I will learn about SQL Query Analyzer!
thanks!
0

Author Commented:
ok I tested the Stored Proceduire and it is returning the correct value. 10.95, 21.90 etc . The problem is on the aspx page, I quess. Except for the ceiling function, this Stored Procedure is identical to one I use to get my shoppingcart total dollar and it returns the decimal correctly.

It has to be something with moving totalweight from the procedure to the web page. Apparantly the webpage is inheriting the ceiling function? Is that possible?
0

Commented:
It's more likely, based on your last results, that it is a type conversion problem, where a decimal gets converted to an int of some form.
0

Commented:
not likely ... can you put up your web page code it is most likely as Mr. Price suggests a conversion occurring.
0

Author Commented:
Here is the code form all related objects

1. (Sub ShipWeightandTotals is from the webpage, I can complete page if you wish. )
2. Public Function GetWeight from .vb busines object ( I experimented with the 10,2 in the totalweight parameter, it made no difference)
3. Stored procedure

1.
Web Page:
Protected totalweight as decimal
Sub ShipWeightandTotals()
dim cart as ASPNET.StarterKit.Commerce.ShoppingCartDB = New ASPNET.StarterKit.Commerce.ShoppingCartDB()
lblshiptotAL.TEXT = String.Format("{0:c}", totalweight)
TotalLbl.Text =  String.Format("{0:c}", cart.GetTotal(cartId)+lblshiptotAL.TEXT)
end sub
2.
Public Function GetWeight(ByVal cartID As String) As Decimal
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingCartWeight", myConnection)

myCommand.CommandType = CommandType.StoredProcedure
Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50)
parameterCartID.Value = cartID

Dim parameterTotalWeight As SqlParameter = New SqlParameter("@TotalWeight", SqlDbType.decimal, 10,2)
parameterTotalWeight.Direction = ParameterDirection.Output

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

3.
Stored Procedure:
@Totalweight=10.95*ceiling(sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20)
DonSurplus2 (12:19:22 PM): lblshiptotAL.TEXT = String.Format("{0:c}", strtotalweight)
DonSurplus2 (12:20:07 PM): Protected strtotalweight as decimal
DonSurplus2 signed off at 12:33:07 PM.
DonSurplus2 signed on at 1:13:30 PM.
DonSurplus2 (1:13:33 PM): @Totalweight=convert(decimal(10,2),10.95)*ceiling(sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20)

DonSurplus2 signed off at 1:26:34 PM.
DonSurplus2 signed on at 4:04:20 PM.
DonSurplus2 (4:04:32 PM): CREATE Procedure CMRC_ShoppingCartWeight
(
@CartID    nvarchar(50),
@Totalweight money OUTPUT

)
AS
DECLARE @X decimal
SELECT

@Totalweight=convert(decimal(10,2),10.95)*ceiling(sum(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20)

FROM
CMRC_ShoppingCart,
CMRC_Products

WHERE
CMRC_ShoppingCart.CartID = @CartID
AND
CMRC_Products.ProductID = CMRC_ShoppingCart.ProductID
GO
0

Author Commented:
Goodmorning! All,

I finally fixed this problem. This may not be the proper way but, I changed the SQLDbType to Money,8 and did the same in the Stored Procedure. I am no getting the correct values.
0

Author Commented:
Ok, Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.