SQL Server Performance

AVG function rounding when shouldn't

Discussion in 'SQL Server 2005 General Developer Questions' started by bhill, May 26, 2009.

  1. bhill New Member

    I have a SQL statment that I'm using the AVG function on, but it's rounding the results to the nearest whole number and I'm not sure why. My query is this:
    SELECT AVG(dbo.fn_GetBusDays(Date_Identified, Pending_Closed_By_Date)) AS WorkDays
    FROM dbo.tblCAP_Capa
    WHERE (Initiated_By_Date BETWEEN '4/1/2009' AND '4/30/2009')
    If it matters, dbo.fn_GetBusDays is a scalar function that returns an integer - it just calculates the number of working days between two dates. Individualy, the record values are 22, 19, and 12 (calculated by the function), which should average out to 17.6666..., but 17 is what shows up when I run it. It even rounds when I divide the COUNT by the SUM ie. SUM(dbo.fn_GetBusDays(Date_Identified, Pending_Closed_By_Date))/COUNT(CAPA_Number). In this case, I need to round to one decimal point (looking for 17.7). I even tried throwing ROUND(x,1) in various spots, but to no avail. Is there a setting somewhere that I am missing or is there something else that is less obvious to me that I am doing wrong. Thanks in advance for any insight.
  2. bhill New Member

    Shortly after this post, I decided to try again and changed the dbo.fn_GetBusDays function to return float instead of int. That did the trick. I'm suprised by the behavior through. I assumed the function ran independently of the querying and that the AVG would just average out the function results irregardless of the functions defined datatype. Apparently they are somehow wrapped together inregards to formatting. Anyways, sorry for the false alarm to anyone who reads this.
  3. FrankKalis Moderator

  4. Madhivanan Moderator

  5. moh_hassan20 New Member

    Try these magic numbers: [;)]
    select 7/6
    select 7/6 * 1.0
    select 1.0 *7/6

    select 7.0/6
  6. Madhivanan Moderator

    <P mce_keep="true">[quote user="moh_hassan20"] <P>&nbsp;Try these magic numbers: <IMG alt=Wink src=""></P><P>select 7/6&nbsp; <BR>select 7/6 <B>* 1.0</B><BR>select <B>1.0</B> *7/6 <BR><BR>select 7.0/6 <BR></P>[/quote]<BR>Havn't you read previous links? [;)]
  7. moh_hassan20 New Member

    [quote user="Madhivanan"]Havn't you read previous links?[/quote]
    yes , i do [;)]
    The example show, extra information that say:
    Take into account order of computation from left to right
    select 7/6 * 1.0
    is different than

    select 1.0 *7/6

Share This Page