Skip to main content
T
trojnfn
Inspiring
November 18, 2009
Answered

Rounding Up

  • November 18, 2009
  • 2 replies
  • 913 views

I am doing a simple calculation and need to round up the output :

Here is my partial query :

SELECT SUM(cycleTime) / COUNT(urdn_number) AS ceiling(cycleTime), count(urdn_number) as totalCount,
  sum(totalValue) as totalCost, Month_Status
   FROM
   ( 
   SELECT distinct a.urdn_number,
   DATEDIFF(day, a.Date_Arrived, b.activity_date) + 1 AS cycleTime,

The cycle time variable needs to round up but it keeps rounding down. What am I doing wrong ? I even had the ceiling before the sum but that still did not work.

For example, my numbers are 206/14 = 14.7 and should round p to 15 but I still get 14 as my output.

Please help, driving me nuts.

This topic has been closed for replies.
Correct answer ilssac

If worse comes to worse just select the two components and do the calculation in the output.

<cfquery name="stuff"...>

SELECT SUM(cycleTime), COUNT(urdn_number)...

...

</cfquery>

<cfoutput query="stuff"...>

#ceiling(cycleTime/urdn_number)#


</cfoutput>

2 replies

ilssac
ilssac
Inspiring
November 18, 2009

Does your database have a ceiling or similar function?

Doing it as you have here is not going to work.  You are just naming the results with a column name "ceiling(cycleTime)"

If you can not do this in the database, it should be pretty easy to do it in the output with ColdFusion functions.

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
November 18, 2009

It looks like the SELECT portion of your query may be the problem.  Try moving the the call to the ceiling function to surround the value in question.

SELECT
     ceiling( SUM(cycleTime) / COUNT(urdn_number) ) AS cycleTime,
    count(urdn_number) as totalCount,
      sum(totalValue) as totalCost, Month_Status

Have you also verified that your database supports the ceiling function?

T
tooMuchTrouble
Inspiring
November 18, 2009

is this sql server? if so, are any of the columns used in your calculations int

data type? sql server will always default to the lowest common denominator, ie

if you do

real_column / int_column

you get back an int result. try casting the int column to real or multiplying by

1.00 to let sql server you want to use reals (or whatever).

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices