I have the following code that checks surface area of a product and spits out a number.
However, it’s very efficient.
How can I write this code with Case When, Then instead of variables and if else?
declare @test decimal(10,2)
select @test = ([WIDTH]/1000)*(HEIGHT]/1000)
from MAIN.SYSADM.[TABLE] where ID=854037 and POS_NR=1
if(@test>1) begin
(
select top(1)360/SUM(CAST([QTY] as int)) from MAIN.SYSADM.[TABLE]
where ID=854037 and POS_NR=1
and BOM_PRODUKT not in (52101,52102,52006,52007,52003,52005,52008,53201,53102)
and (STL_PRODGRP in (26,27,28,30,33,35,412,413,415,425,426,427) or BOM_PRODUKT =50002) GROUP by BOM_NODE )
end
else
begin
(
select top(1)180/SUM(CAST([QTY] as int)) from MAIN.SYSADM.[TABLE]
where ID=854037 and POS_NR=1
and BOM_PRODUKT not in (52101,52102,52006,52007,52003,52005,52008,53201,53102)
and (STL_PRODGRP in (26,27,28,30,33,35,412,413,415,425,426,427) or BOM_PRODUKT =50002) GROUP by BOM_NODE)
end
I tried this but the top(1) and GROUP by doesn’t work
select (select top(1) (
case when cast(([WIDTH]/1000)*([HEIGHT]/1000) as decimal(10,2))>1
then
360/sum(CAST([QTY] as int ))
else 180/sum(CAST([QTY] as int ))
end ))
from MAIN.SYSADM.[TABLE] where [ID]=854037 and POS_NR=1
and BOM_PRODUKT not in (52101,52102,52006,52007,52003,52005,52008,53201,53102)
and (STL_PRODGRP in (26,27,28,30,33,35,412,413,415,425,426,427) or BOM_PRODUKT =50002 ) GROUP by BOM_NODE
New contributor
andreas p is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1