SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

4 rows affected

with cte1 as (
  select code original_code, a1.*
    , v.available
    , case when a1.minimum_target > 0 then case when a1.purchasing_request > v.available then v.available else a1.purchasing_request end else 0 end assigned
    , purchasing_request - case when a1.minimum_target > 0 then case when a1.purchasing_request > v.available then v.available else a1.purchasing_request end else 0 end purchasing_request_new
    , 0 order_by
  from alternatives a1
  cross apply (values (a1.amount_in_hand - a1.minimum_target)) v (available)
  cross apply (values (case when a1.minimum_target > 0 then case when a1.purchasing_request > v.available then v.available else a1.purchasing_request end else 0 end)) z (assigned)
  where purchasing_request > 0
), cte2 as (
  select cte1.original_code, a2.code, cte1.alternative_1, cte1.alternative_2, cte1.alternative_3
    , a2.minimum_target, a2.amount_in_hand, a2.purchasing_request
    , v.available
    , case when a2.minimum_target > 0 then case when cte1.purchasing_request_new > v.available then v.available else cte1.purchasing_request_new end else 0 end assigned
    , cte1.purchasing_request_new - case when a2.minimum_target > 0 then case when cte1.purchasing_request_new > v.available then v.available else cte1.purchasing_request_new end else 0 end purchasing_request_new
    , 1 order_by
  from alternatives a2
  cross apply (values (a2.amount_in_hand - a2.minimum_target)) v (available)
  join cte1 on cte1.alternative_1 = a2.code
  where cte1.purchasing_request_new > 0
), cte3 as (
  select cte2.original_code, a3.code, cte2.alternative_1, cte2.alternative_2, cte2.alternative_3
    , a3.minimum_target, a3.amount_in_hand, a3.purchasing_request
    , v.available
    , case when a3.minimum_target > 0 then case when cte2.purchasing_request_new > v.available then v.available else cte2.purchasing_request_new end else 0 end assigned
    , cte2.purchasing_request_new - case when a3.minimum_target > 0 then case when cte2.purchasing_request_new > v.available then v.available else cte2.purchasing_request_new end else 0 end purchasing_request_new
    , 2 order_by
  from alternatives a3
  cross apply (values (a3.amount_in_hand - a3.minimum_target)) v (available)
  join cte2 on cte2.alternative_2 = a3.code
  where cte2.purchasing_request_new > 0
), cte4 as (
  select cte3.original_code, a4.code, cte3.alternative_1, cte3.alternative_2, cte3.alternative_3
    , a4.minimum_target, a4.amount_in_hand, a4.purchasing_request
    , v.available
    , case when a4.minimum_target > 0 then case when cte3.purchasing_request_new > v.available then v.available else cte3.purchasing_request_new end else 0 end assigned
    , cte3.purchasing_request_new - case when a4.minimum_target > 0 then case when cte3.purchasing_request_new > v.available then v.available else cte3.purchasing_request_new end else 0 end purchasing_request_new
    , 3 order_by
  from alternatives a4
  cross apply (values (a4.amount_in_hand - a4.minimum_target)) v (available)
  join cte3 on cte3.alternative_3 = a4.code
  where cte3.purchasing_request_new > 0
)
select *,
  case when order_by = 1 then min(purchasing_request_new) over (partition by original_code) else 0 end purchase_request_final
from (
  select * from cte1
  union all
  select * from cte2
  union all
  select * from cte3
  union all
  select * from cte4
) x
order by order_by;

original_code	code	alternative_1	alternative_2	alternative_3	minimum_target	amount_in_hand	purchasing_request	available	assigned	purchasing_request_new	order_by	purchase_request_final
D156	D156	B156	C156	A156	0	0	150	0	0	150	0	0
D156	B156	B156	C156	A156	30	65	0	35	35	115	1	104
D156	C156	B156	C156	A156	40	50	0	10	10	105	2	0
D156	A156	B156	C156	A156	15	16	0	1	1	104	3	0