-- Transact SQL: case when 1=1 then 0.5 else ceiling(sh) end /* returns 1 (!) why? */
declare @T table (h decimal(2,1))
insert @T (h) values (1.0)
select
case when 1=1 then 0.5 else ceiling(sh) end /* returns 1 (!) why? */
from @T T1
join (select sum(h) as sh from @T )T2 on 1 = 1
The answer is nothing to do with the int datatype
0.5 has a dataype of decimal(1,1)CEILING on a decimal(p,s) returns a result of type
decimal(p,0) SUM on a decimal(p,s) returns a result of
type decimal(38,s) CASE expression that can return
decimal(p1,s1) or decimal(p2,s2) the result will use the same rules as when UNION-ing these data types and have precision (*)
of max(s1, s2) + max(p1-s1, p2-s2) and scale of max(s1, s2)
*The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it is reduced to 38, and the corresponding scale is reduced to try to prevent the integral part of a result from being truncated. (source)
So your column h has datatype of decimal(2,1), the datatype when SUM is applied is decimal(38,1), the datatype of CEILING applied to that is decimal(38,0). Then you use that in a CASE expression with decimal(1,1)
max(s1, s2) + max(p1-s1, p2-s2)
max( 0, 1) + max( 38, 0) = 1 + 38 = 39
And
max(s1, s2) = max(0, 1) = 1
So the desired result datatype would be decimal(39,1). This is larger than 38 so you get the scale reduction described above and end up with decimal(38,0) - 0.5 is rounded to 1 when cast to that datatype.
If you would rather keep the precision of the final result you can use
case when 1=1 then 0.5 else CAST(ceiling(sh) AS decimal(38,1)) end
There is a miniscule additional risk of overflow with this but to be hit by it the sum would need to add up to one of the following values
9999999999999999999999999999999999999.59999999999999999999999999999999999999.69999999999999999999999999999999999999.79999999999999999999999999999999999999.89999999999999999999999999999999999999.9such that the SUM itself fits into 38,1 but CEILING doesn't.