Truncate/Round varchar columns in SQL

Question

I have 2 columns Total and OccupiedBeds which are varchar datatype.

I need to calculate Occupancy = OccupiedBeds/Total and using below code:

CASE 
        WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
            THEN 0
        ELSE cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)
        END AS Occupancy

o/p:

Total OccupiedBeds Occupancy
0          0    0.00000000000000
0          0    0.00000000000000
0          0    0.00000000000000
8         736   10.86956521739130
7         638   10.97178683385579
4         367   10.89918256130790
6         460   13.04347826086956

I need to truncate/Round the Occupancy so it can be shown as % on a chart.

I tried replacing float with decimal(19,2) but showing below results:

Total OccupiedBeds Occupancy
0            0      0
0            0      0
8            736    10.8695652173913
7            638    10.9717868338558
4            367    10.8991825613079
6            460    13.0434782608696

Any ideas how to accomplish this?

Answer 1

You can basically use the ROUND function, rather than changing the data type:

CASE 
    WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
        THEN 0
    ELSE ROUND((cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)), 2)
    END AS Occupancy

UPD:

Your Occupancy formula looks a bit strange too, perhaps you need to calculate it as OccupiedBeds / Total * 100:

CASE 
    WHEN isnull(cast([Total] AS INT), 0) = 0
        THEN 0
    ELSE ROUND((cast([OccupiedBeds] AS float) / cast([Total] AS float) * 100), 2)
    END AS Occupancy