Rounding Up

[trojnfn]

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.

[JR__Bob__Dobbs-qSBHQ2]

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?

[tooMuchTrouble]

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).

[trojnfn]

I tried moving the ceiling to the sum and it still did not work.

I even tried #ceiling(cycleTime)# in the output and that did not work either.

I am on SQL and it has a ceiling and/or round fucntion, I gooled it. But still cannot get it to work.

[ilssac · Accepted solution]

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>

[ilssac]

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.