sql server 2008 - ROUND TSQL - .5 not rounding up -

in select statement have following case statement need round 0 decimal places return int:

select          reportingdate     ,   portfolioid     ,   portfolionme     ,   '16'                                                                [rank]     ,   'average credit rating'                                             rating     ,   round(sum((percentage * case                                     when wt.issuetype1 in ('050','110') 15.5                                     else xref.internal_value2                                 end ))/sum(percentage),0)   [weight]  @worktablesa wt      left outer join dp_crossreference xref         on xref.internal_value = wt.rating         , xref.codeset_type_id = 10013         , xref.originator_id = 'kurtosysextract'            group    wt.reportingdate         ,   wt.portfolioid         ,   wt.portfolionme   order    wt.reportingdate         ,   wt.portfolioid 

there 1 particular result returned isn't correct. value returns in sum before round 15.5. round returning value of 15, when i'm expecting rounded 16.

to give insight data types, percentage float , internal_value2 nvarchar holds mixture of int (1,2,3) , 1 decimal (15.5).

i've tried amending values floats , still 15. i've done following , returns 16:

select round(15.5,0) 

any in trying test alternative appreciated because i'm out of ideas?

1. with round function ok, select round(15.5,0) return 16

but, example, if required round values starting 0.39 1 (as in example can 15.49 16) use method below

declare @val float = 15.49  select ( case when @val - convert(int, @val) >= 0.49                     ceiling(@val)               else round(@val, 0)  end )   newval 

this variant applicable, since 2012 version

select iif( @val - convert(int, @val) >= 0.49, ceiling(@val), round(@val, 0))  newval 

so final query should this:

select t.reportingdate       ,t.portfolioid       ,t.portfolionme       ,t.[rank]       ,t.rating       ,(case    when t.[weight] - convert(int, t.[weight]) >= 0.49                 ceiling(t.[weight])                 else round(t.[weight], 0) end) [weight] (   select reportingdate                ,portfolioid                ,portfolionme                ,'16' [rank]                ,'average credit rating' rating                ,round(sum((percentage * case when wt.issuetype1 in ('050','110')                                              15.5                                              else xref.internal_value2 end))                          / sum(percentage), 0) [weight]          @worktablesa wt                     left outer join dp_crossreference xref                              on xref.internal_value = wt.rating                                 , xref.codeset_type_id = 10013                                 , xref.originator_id = 'kurtosysextract'         group wt.reportingdate                 ,wt.portfolioid                 ,wt.portfolionme     ) t order t.reportingdate         ,t.portfolioid 

---

2. if faced problem select round(15.49999,0) method you

   declare @val float = 15.49999  select round(convert(numeric(15,1),@val),0) 

which return 16

so, final query should this

select          reportingdate     ,   portfolioid     ,   portfolionme     ,   '16'                                                                [rank]     ,   'average credit rating'                                             rating     ,   round(convert(numeric(15,1),sum((percentage * case                                     when wt.issuetype1 in ('050','110') 15.5                                     else xref.internal_value2                                 end ))/sum(percentage)),0)   [weight]  @worktablesa wt      left outer join dp_crossreference xref         on xref.internal_value = wt.rating         , xref.codeset_type_id = 10013         , xref.originator_id = 'kurtosysextract'            group    wt.reportingdate         ,   wt.portfolioid         ,   wt.portfolionme