Solved

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

Posted on 2004-09-08
29
363 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
  • 12
  • 8
  • 3
  • +2
29 Comments
 
LVL 7

Expert Comment

by:wesbird
Comment Utility
I think the / 20 is in the wrong place:

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

Author Comment

by:dplsr
Comment Utility
Nope that divides CMRC_ShoppingCart.Quantity
0
 
LVL 37

Expert Comment

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

by:JohnBPrice
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
declare @x  decimal(10,2) is returning 13 where it should be 10.95 too. thanks!
0
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:JohnBPrice
Comment Utility
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
Comment Utility
In the stored procedure it is money

@Totalweight Money
0
 

Author Comment

by:dplsr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, Thanks!
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
Comment Utility
PAQed, with points refunded (0)

GhostMod
Community Support Moderator
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
userCompare  challenge 3 61
White board coding practice 3 60
advertisement module in core php 4 78
Path to Python 9 35
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now