Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2004-09-08
Medium Priority
375 Views
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
0
Question by:dplsr
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 8
• 3
• +2

LVL 7

Expert Comment

ID: 12009464
I think the / 20 is in the wrong place:

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

Author Comment

ID: 12009754
Nope that divides CMRC_ShoppingCart.Quantity
0

LVL 37

Expert Comment

ID: 12010788
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

LVL 16

Expert Comment

ID: 12011054
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

LVL 7

Expert Comment

ID: 12015681
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 Comment

ID: 12016038
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 Comment

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

LVL 37

Expert Comment

ID: 12017424
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 Comment

ID: 12017685
Sorry I missed this one!

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

0

LVL 16

Expert Comment

ID: 12017966
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

LVL 16

Expert Comment

ID: 12017985
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

LVL 16

Expert Comment

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

Author Comment

ID: 12018468
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

LVL 16

Expert Comment

ID: 12018787
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 Comment

ID: 12018960
In the stored procedure it is money

@Totalweight Money
0

Author Comment

ID: 12019029
i now have

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

still get \$11.00
0

LVL 16

Expert Comment

ID: 12019075
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

LVL 16

Expert Comment

ID: 12019129
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 Comment

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

Author Comment

ID: 12020349
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

LVL 16

Expert Comment

ID: 12020390
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

LVL 37

Expert Comment

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

Author Comment

ID: 12020831
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 Comment

ID: 12043431
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 Comment

ID: 12048679
Ok, Thanks!
0

LVL 1

Accepted Solution

GhostMod earned 0 total points
ID: 12351593
PAQed, with points refunded (0)

GhostMod
Community Support Moderator
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and â€¦
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in aâ€¦
###### Suggested Courses
Course of the Month11 days, 8 hours left to enroll