SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table [TABLE] (
    ID int,
    POS_NR int,
    BOM_PRODUKT int,
    STL_PRODGRP int,
    BOM_NODE int,
    QTY int,
    WIDTH float,
    HEIGHT float
)

insert [TABLE]
values
    (854037, 1, 50000, 26, 1, 10, 750.0, 2000.0),  -- (WIDTH/1000)*(HEIGHT/1000) = 1.5
    (854037, 1, 50000, 26, 1, 20, 750.0, 2000.0),
    (854037, 1, 50000, 26, 2,  5, 750.0, 2000.0)
    
3 rows affected

select --top(1)
    BOM_NODE,
    SUM(QTY) as SumQty,
    MAX((WIDTH/1000)*(HEIGHT/1000)) as MaxArea,
    case when (
        select MAX((WIDTH/1000)*(HEIGHT/1000))
        from [TABLE]
        where ID=854037 and POS_NR=1
        ) > 1
    then 360/SUM(CAST(QTY as int))
    else 180/SUM(CAST(QTY as int))
    end as Result 
from [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


BOM_NODE
SumQty
MaxArea
Result


1
30
1.5
12

2
5
1.5
72


update [TABLE]
set WIDTH = 250.0  -- Now (WIDTH/1000)*(HEIGHT/1000) = 0.5
3 rows affected

select --top(1)
    BOM_NODE,
    SUM(QTY) as SumQty,
    MAX((WIDTH/1000)*(HEIGHT/1000)) as MaxArea,
    case when (
        select MAX((WIDTH/1000)*(HEIGHT/1000))
        from [TABLE]
        where ID=854037 and POS_NR=1
        ) > 1
    then 360/SUM(CAST(QTY as int))
    else 180/SUM(CAST(QTY as int))
    end as Result 
from [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


BOM_NODE
SumQty
MaxArea
Result


1
30
0.5
6

2
5
0.5
36


declare  @test decimal(10,2) 
select @test = ([WIDTH]/1000)*([HEIGHT]/1000)
from [TABLE]  where ID=854037 and POS_NR=1 


if(@test>1) begin

(
select top(1)360/SUM(CAST([QTY] as int)) from  [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  [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


(No column name)


6


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 [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 
Msg 8120 Level 16 State 1 Line 2
Column 'TABLE.WIDTH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120 Level 16 State 1 Line 2
Column 'TABLE.HEIGHT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select (select top(1) (
case when cast(MAX(([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 [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 


(No column name)


6

36