Solved

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

Posted on 2004-09-08
29
364 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bunnyEars challenge 6 103
count8 challlenge 13 119
mapShare challenge 13 91
Q2. How to run DAX query from SSMS. I don’t see any DAX query type. 1 49
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
This is an explanation of a simple data model to help parse a JSON feed
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

864 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

22 Experts available now in Live!

Get 1:1 Help Now