SQL Sever如何在计算结果中增加一定百分比的损耗值，比如求和结果理论用量为10，需要加百分之十五的损耗率，就是用11.5

Question

源代码：select a.OrderInfo1 '合同号',a.OrderInfo2 '柜体名称',a.OrderInfo3,a.OrderId '生产编号',a.Client '客户地址',a.Employee '拆单员',
case when CHARINDEX('银镜',Cpid)>0 then Cpid when cpid='铝箔纸' then '1404000100' when unit='米兰层板灯（拉丝金色）' then'1406000122' when unit='铝合金踢脚板（黑色）' then '1404000046' when unit='5mm透明玻璃' then '1306000097' else c.DetailName end DetailName,a.OrderCnt,
'五金' as [Datatype],case when Length>0 then cast(Length as nvarchar) else ' ' end,Width,c.Matname,case when unit = '3mm无铜磨边银镜' then '1405000063' else MatProducer end,case when DetailName='0501130027'then 1 else sum(Cnt) end as qty,
case when SysType=7 then MatProducer when c.ArticleID='BL_TM_05' then '块' when DetailName='0501130027'then '支' when detailname='1403000240' then '支' when unit='铝合金踢脚板（黑色）' then '块' when unit='米兰层板灯（拉丝金色）' then'套' when cpid='铝箔纸' then '块' when detailname='3mm无铜磨边银镜' then '块' else c.ArticleID end,
case when DetailName='1404000015' or DetailName='自攻丝40X30' then '自攻丝4X30' when detailname = '1403000240' then '铝合金踢脚板(铝色)' else Unit end unit,c.Thk,
case when Length>0 then cast(round(Length,0) as nvarchar)+'*'+ cast(round(Width,0) as nvarchar) else ' ' end ,(sum(Cnt) * Orderinfo3) as [合计],'' from T_BOM_Order a
inner join T_BOM_Item b on a.Id=b.Order_Id
inner join T_BOM_ItemDetail c on b.Id=c.Product_Id
where a.OrderId='csjstm' and SysType<>3
group by a.OrderInfo1,a.OrderInfo2,a.OrderInfo3,a.OrderId ,a.Client ,a.Employee,c.DetailName,a.OrderCnt,Length,Width,c.Matname,c.ArticleID,Unit,c.thk,SysType,MatProducer,Cpid

图片

当unit字符中包含金属卡条字眼的时候，333×0需要增加百分之十五的损耗，就是需要499.5×0

Answer 1

你这个sql里面其实已经有类似的写法了

case when CHARINDEX('金属卡条',unit)>0 then 
          cast(round(Length*1.15, 0) as nvarchar) + '*' +
          cast(round(Width, 0) as nvarchar)
         end