SQL Server 2022

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

select @@version;
create table tbl_order (
  order_id int primary key,
  order_date datetime,
  shipping_date datetime
);
create table tbl_order_item (
  order_id int,
  product_id int,
  quantity int,
  primary key(order_id, product_id)
);
create table tbl_stock_item (
  product_id int,
  quantity int
);
create table tbl_supply_order (
  supply_order_id int primary key,
  expected_delivery datetime
);
create table tbl_supply_order_item (
  supply_order_id int,
  product_id int,
  quantity int,
  primary key (supply_order_id, product_id)
);

-- some sample data

insert into tbl_order values 
(1, '2024-11-01', '2024-11-06'),
(2, '2024-11-01', '2024-11-06'),
(3, '2024-11-01', '2024-11-07'),
(4, '2024-11-02', '2024-11-07'),
(5, '2024-11-02', '2024-11-08');

insert into tbl_order_item values 
(1, 1, 1),
(1, 2, 1),
(2, 1, 2),
(2, 2, 1),
(3, 1, 2),
(3, 2, 2),
(4, 2, 2),
(5, 1, 1);

insert into tbl_supply_order values 
(1, '2024-11-11'),
(2, '2024-11-15'),
(3, '2024-11-20');

insert into tbl_supply_order_item values 
(1, 1, 2),
(1, 2, 3),
(2, 1, 2),
(2, 2, 1),
(3, 1, 2),
(3, 2, 2);

create table expected_results (
  order_id int,
  product_id int,
  matched_supply_orders varchar(255),
  expected_delivery datetime
);

insert into expected_results values 
(1, 1, '1', '2024-11-11'),
(2, 1, '1,2', '2024-11-15'),
(3, 1, '2,3', '2024-11-20'),
(5, 1, '3', '2024-11-20'),
(1, 2, '1', '2024-11-11'),
(2, 2, '1', '2024-11-11'),
(3, 2, '1,2', '2024-11-15'),
(4, 2, '3', '2024-11-20');

select 
  'Customer orders' as description,
  *
from tbl_order o
inner join tbl_order_item oi on o.order_id = oi.order_id
order by o.order_id, oi.product_id;

select
  'Supply orders' as description,
  *
from tbl_supply_order o
inner join tbl_supply_order_item oi on o.supply_order_id = oi.supply_order_id
order by o.supply_order_id, oi.product_id;

select
  'Expected results' as description,
  *,
  max(expected_delivery) over (partition by order_id) as expected_order_delivery_date
from expected_results
order by order_id, expected_delivery;

(No column name)
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

description	order_id	order_date	shipping_date	order_id	product_id	quantity
Customer orders	1	2024-11-01 00:00:00.000	2024-11-06 00:00:00.000	1	1	1
Customer orders	1	2024-11-01 00:00:00.000	2024-11-06 00:00:00.000	1	2	1
Customer orders	2	2024-11-01 00:00:00.000	2024-11-06 00:00:00.000	2	1	2
Customer orders	2	2024-11-01 00:00:00.000	2024-11-06 00:00:00.000	2	2	1
Customer orders	3	2024-11-01 00:00:00.000	2024-11-07 00:00:00.000	3	1	2
Customer orders	3	2024-11-01 00:00:00.000	2024-11-07 00:00:00.000	3	2	2
Customer orders	4	2024-11-02 00:00:00.000	2024-11-07 00:00:00.000	4	2	2
Customer orders	5	2024-11-02 00:00:00.000	2024-11-08 00:00:00.000	5	1	1

description	supply_order_id	expected_delivery	supply_order_id	product_id	quantity
Supply orders	1	2024-11-11 00:00:00.000	1	1	2
Supply orders	1	2024-11-11 00:00:00.000	1	2	3
Supply orders	2	2024-11-15 00:00:00.000	2	1	2
Supply orders	2	2024-11-15 00:00:00.000	2	2	1
Supply orders	3	2024-11-20 00:00:00.000	3	1	2
Supply orders	3	2024-11-20 00:00:00.000	3	2	2

description	order_id	product_id	matched_supply_orders	expected_delivery	expected_order_delivery_date
Expected results	1	1	1	2024-11-11 00:00:00.000	2024-11-11 00:00:00.000
Expected results	1	2	1	2024-11-11 00:00:00.000	2024-11-11 00:00:00.000
Expected results	2	2	1	2024-11-11 00:00:00.000	2024-11-15 00:00:00.000
Expected results	2	1	1,2	2024-11-15 00:00:00.000	2024-11-15 00:00:00.000
Expected results	3	2	1,2	2024-11-15 00:00:00.000	2024-11-20 00:00:00.000
Expected results	3	1	2,3	2024-11-20 00:00:00.000	2024-11-20 00:00:00.000
Expected results	4	2	3	2024-11-20 00:00:00.000	2024-11-20 00:00:00.000
Expected results	5	1	3	2024-11-20 00:00:00.000	2024-11-20 00:00:00.000