Solved

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

Posted on 2004-09-08
29
369 Views
Last Modified: 2010-04-17
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
Comment
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
  • Learn & ask questions
  • 12
  • 8
  • 3
  • +2
29 Comments
 
LVL 7

Expert Comment

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

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

Author Comment

by:dplsr
ID: 12009754
Nope that divides CMRC_ShoppingCart.Quantity
0
 
LVL 37

Expert Comment

by:gregoryyoung
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:JohnBPrice
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

by:wesbird
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

by:dplsr
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

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

Expert Comment

by:gregoryyoung
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

by:dplsr
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

by:JohnBPrice
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

by:JohnBPrice
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

by:JohnBPrice
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

by:dplsr
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

by:JohnBPrice
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

by:dplsr
ID: 12018960
In the stored procedure it is money

@Totalweight Money
0
 

Author Comment

by:dplsr
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

by:JohnBPrice
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

by:JohnBPrice
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

by:dplsr
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

by:dplsr
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

by:JohnBPrice
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

by:gregoryyoung
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

by:dplsr
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.
Business object: ( .vb file)
       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
            myCommand.Parameters.Add(parameterCartID)

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

            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

by:dplsr
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

by:dplsr
ID: 12048679
Ok, Thanks!
0
 
LVL 1

Accepted Solution

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

GhostMod
Community Support Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question