Why is SQL Server truncating my values from a decimal column?

50 views Asked by At

I'm writing a query which do some calculations over some tables to return to my application, after debugging I've found out that the undesired truncation is being done directly in SQL Server instead of my app.

I've got those two tables and columns (considering both valued and pop_valued are decimal(38,9)):

select valued 
from dbname.dbo.TABLE_SUM
where market_id_2 = 103 -- returns 2454356.000000000

select pop_valued 
from dbname.dbo.TABLE_POP
where market_id_1 = 103 -- 8035229.000000000

And the query that does the calculation is:

SELECT 
    sm.totalsortkey_bigid_0     AS totalsortkey_bigid_0,
    COALESCE(sm.market_id_2, 0) AS market_id_2,
    sm.advertiser_id_3          AS advertiser_id_3,
    sm.time_day_4               AS time_day_4,

    SUM(100.0 * (sm.valued /
          NULLIF(b.pop_valued, 0))) AS valued

FROM 
    dbname.dbo.TABLE_SUM sm
JOIN 
    dbname.dbo.TABLE_POP b ON sm.market_id_2 = b.market_id_1
                           AND sm.population_id_1 = b.population_id_0
GROUP BY 
    sm.totalsortkey_bigid_0,
    sm.market_id_2,
    sm.advertiser_id_3,
    sm.time_day_4

where, for market_id = 103 the 'valued' is displayed as: 30.544900

But if I do this test query with the same values:

select sum(100 * (2454356.000000000 / NULLIF(8035229.000000000, 0))) 

then I get the desired result which is: 30.544941531846821043.

I've found some similar questions like this one and tried to apply the cast(val as decimal(38,9)) but it didn't worked. So my question is how to avoid this truncate behavior on the query to return the desired calculation?

0

There are 0 answers