CASE expression returns wrong value when using CEILING
To reproduce the problem:

SELECT *, (CASE
    WHEN IsGun=1 THEN CEILING(Price1Avg)
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;


What happens here is that CEILING(PQ.Price1Avg) produces a numeric(38, 0).

According to the documentation, the output type of CEILING() is of the same base datatype as the input, although the scale (the number of decimals) may change, which is what happens here.


The AVG() function, in my tests, returns numeric(38, 6).
The CEILING() function on that column, however, outputs numeric(38, 0):


To verify:

SELECT CEILING(CAST(123.45 AS numeric(38, 6)))


As a workaround, you could explicitly convert the output of the CEILING() function, which should give you the correct results:

SELECT *, (CASE
    WHEN IsGun=1 THEN CAST(CEILING(Price1Avg) AS numeric(8, 2)) -- Explicit CAST.
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;