SQL Server Update not updating the decimals

I am trying to update a column with DECIMAL(5,2) datatype. However, SQL Server seems to be rounding it and not storing the decimal places.

For example, one of the values is 1.53 and its getting stored as 1.00 in the table.

Here's the query

DECLARE @MaxLike DECIMAL (5,2), @MaxComment DECIMAL (5,2), @MaxFavourite DECIMAL (5,2)
SET @MaxLike = 1.0*100/(SELECT MAX(z.MaxLike) FROM (SELECT(COUNT(COALESCE(Liked,0))) AS MaxLike FROM Design_LikeRating GROUP BY DesignID) z)
SET @MaxComment = 1.0*100/(SELECT MAX(z.MaxComment) FROM (SELECT(COUNT(COALESCE(CommentID,0))) AS MaxComment FROM Comment_CommentDetail WHERE IsDeleted = 0 GROUP BY OverallParentGUID) z)
SET @MaxFavourite = 1.0*100/(SELECT MAX(z.MaxFavourite) FROM (SELECT (COUNT(COALESCE(DesignID,0))) AS MaxFavourite FROM Design_Favourite GROUP BY DesignID) z)


-- VoteCount and Rate
DECLARE @table1 TABLE (DesignID INT PRIMARY KEY, Rate DECIMAL(5,2))
INSERT INTO @table1
SELECT DesignID, CAST (Rate AS DECIMAL(5,2)) AS Rate
FROM 
(
SELECT  DesignID, COUNT(COALESCE(Liked,0.0)) AS Rate
FROM Design_LikeRating WITH (NOLOCK)
GROUP BY DesignID
) z

-- FavCount
DECLARE @table2 TABLE (DesignID INT PRIMARY KEY, FavCount DECIMAL(5,2))
INSERT INTO @table2
SELECT DesignID, CAST (FavCount AS DECIMAL (5,2)) AS FavCount
FROM
(
SELECT DesignID, COUNT(COALESCE(DesignID,0.0)) AS FavCount 
FROM Design_Favourite WITH (NOLOCK)
GROUP BY DesignID
) x

-- CommentCount
DECLARE @table3 TABLE (DesignGUID UNIQUEIDENTIFIER PRIMARY KEY, CommentCount DECIMAL(5,2))
INSERT INTO @table3
SELECT OverallParentGUID, CAST (CommentCount AS DECIMAL(5,2)) AS CommentCount
FROM
(
SELECT OverallParentGUID, COUNT(DISTINCT COALESCE(x.DesignID,0.0)) AS CommentCount 
FROM Comment_CommentDetail z WITH (NOLOCK) 
    INNER JOIN Design_DesignDetail x WITH (NOLOCK) ON x.DesignGUID = z.OverallParentGUID
WHERE z.IsDeleted = 0开发者_StackOverflow AND x.UserIDInt != z.UserIDInt
GROUP BY OverallParentGUID
) y
;

WITH CTE AS
            (
            SELECT (COALESCE((1.0 * z.Rate*@MaxLike),0.0) + COALESCE((1.0 * y.FavCount*@MaxFavourite),0.0) + COALESCE((1.0 * x.CommentCount*@MaxFavourite),0.0)) AS PopularityScore, a.DesignID
            FROM Design_DesignDetail a 
                LEFT JOIN @table1 AS z ON z.DesignID = a.DesignID 
                LEFT JOIN @table2 AS y ON y.DesignID = a.DesignID 
                LEFT JOIN @table3 AS x ON x.DesignGUID = a.DesignGUID 
            WHERE DesignID > 300000 and DesignID = 444409
            )

UPDATE  Design_DesignDetail
SET     PopularityScore =  z.PopularityScore
FROM    Design_DesignDetail a 
        INNER JOIN CTE z  on z.DesignID = a.DesignID

UPDATE I'll stop trying to be clever and post the whole query here. As you can see, its got a little bit of everyone's answer but it still isnt working!

This expression must give an int or a decimal with scale 0 (no dec places)

(ISNULL((z.Rate*@MaxLike),0) + ISNULL((y.FavCount*@MaxFavourite),0) + ISNULL((x.CommentCount*@MaxFavourite),0))

I'm guessing you'd need to CAST to float inside each ISNULL without knowing the datatypes

And please don't use NOLOCK like that

Edit, after full code update

Your COUNT aggregates will return int values so your decimal(5,2) values probably have xxx.00 in them.

Some comments added to @table1 load

INSERT INTO @table1
SELECT DesignID,
-- gbn The CAST changes 2 to 2.00
CAST (Rate AS DECIMAL(5,2)) AS Rate
FROM 
(
SELECT  DesignID,
     -- gbn COALESCE does nothing. COUNT gives int and simply counts non-null values
     COUNT(COALESCE(Liked,0.0)) AS Rate 
FROM Design_LikeRating WITH (NOLOCK)
GROUP BY DesignID
) z

See my answer here Count(*) vs Count(1) for more explanation

You are probablly suffering somewhere from integer math.

And never use float when you are doing a calulation unless you like rounding errors.

What are the types of z.Rate, @MaxLike, y.FavCount, @MaxFavourite, and x.CommentCount?

I bet y.FavCount and x.CommentCount are ints.

SELECT ISNULL(1.0 * z.Rate*@MaxLike, 0.0) +
       ISNULL(1.0 * y.FavCount*@MaxFavourite, 0.0) + 
       ISNULL(1.0 * x.CommentCount*@MaxFavourite, 0.0) AS PopularityScore, a.DesignID