英文:
Truncate/Round varchar columns in SQL
问题
我有2列Total和OccupiedBeds，它们的数据类型是varchar。
我需要计算Occupancy = OccupiedBeds/Total，使用下面的代码：
CASE
WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
THEN 0
ELSE cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)
END AS Occupancy
输出：
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
我需要截断/四舍五入Occupancy，以便在图表上显示为百分比。
我尝试将float替换为decimal(19,2)，但结果如下所示：
Total OccupiedBeds Occupancy
0 0 0
0 0 0
8 736 10.87
7 638 10.97
4 367 10.90
6 460 13.04
有什么方法可以实现这个目标吗？
英文:
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?
答案1
得分: 2
你基本上可以使用 ROUND 函数，而不需要更改数据类型：
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：
你的 Occupancy 公式看起来也有点奇怪，也许你需要计算为 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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论