SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table transactions
(
  order_id int,
  id int,
  quantity int,
  cancelled_date date,
  created_date date,
  cancelled bit
);

insert into transactions (order_id, id, quantity, cancelled_date, created_date, cancelled) values
(100000, 1, 100, null        , '2020-10-10', 0),
(100000, 2, 200, '2020-10-11', '2020-10-10', 1),
(100000, 3,  50, null        , '2020-10-12', 0),
(100000, 4, 150, null        , '2020-10-12', 0),
(100001, 5, 300, '2020-10-12', '2020-10-11', 1),
(100001, 6, 300, null        , '2020-10-13', 0),
(100001, 7,  50, null        , '2020-10-14', 0);

select * from transactions;


order_id
id
quantity
cancelled_date
created_date
cancelled


100000
1
100
null
2020-10-10
False

100000
2
200
2020-10-11
2020-10-10
True

100000
3
50
null
2020-10-12
False

100000
4
150
null
2020-10-12
False

100001
5
300
2020-10-12
2020-10-11
True

100001
6
300
null
2020-10-13
False

100001
7
50
null
2020-10-14
False


with cte_cancel as
(
  select t.order_id,
         t.id, -- using id to get transaction order (alternative would be created_date, but then what if an order is created and cancelled on the same day?)
         t.quantity
  from transactions t
  where t.cancelled = 1
)
select c.order_id,
       c.id as cancelled_id,
       c.quantity,
       t.id as substitute_id,
       t.quantity,
       sum(t.quantity) over(partition by t.order_id
                            order by t.id
                            rows between unbounded preceding and current row) as qty_sum,
       case when sum(t.quantity) over(partition by t.order_id
                                      order by t.id
                                      rows between unbounded preceding and current row) <= c.quantity
            then c.id end as parent_order_id
from cte_cancel c
join transactions t
  on t.order_id = c.order_id
where t.cancelled = 0
  and t.id > c.id
order by c.id, t.id;


order_id
cancelled_id
quantity
substitute_id
quantity
qty_sum
parent_order_id


100000
2
200
3
50
50
2

100000
2
200
4
150
200
2

100001
5
300
6
300
300
5

100001
5
300
7
50
350
null


-- minimal version
with cte_cancel as
(
  select t.order_id,
         t.id,
         t.quantity
  from transactions t
  where t.cancelled = 1
),
cte_match as
(
  select c.order_id,
         c.id as cancelled_id,
         t.id as substitute_id,
         case when sum(t.quantity) over(partition by t.order_id
                                        order by t.id
                                        rows between unbounded preceding and current row) <= c.quantity
              then c.id end as parent_order_id
  from cte_cancel c
  join transactions t
    on t.order_id = c.order_id
  where t.cancelled = 0
    and t.id > c.id
)
select m.order_id,
       m.cancelled_id,
       m.substitute_id
from cte_match m
where m.parent_order_id is not null
order by m.order_id,
         m.cancelled_id;


order_id
cancelled_id
substitute_id


100000
2
3

100000
2
4

100001
5
6


-- version with dates
with cte_cancel as
(
  select t.order_id,
         t.id,
         t.cancelled_date,
         t.quantity
  from transactions t
  where t.cancelled = 1
),
cte_match as
(
  select c.order_id,
         c.id as cancelled_id,
         c.cancelled_date,
         t.id as substitute_id,
         t.created_date as substitute_date,
         case when sum(t.quantity) over(partition by t.order_id
                                        order by t.id
                                        rows between unbounded preceding and current row) <= c.quantity
              then c.id end as parent_order_id
  from cte_cancel c
  join transactions t
    on t.order_id = c.order_id
  where t.cancelled = 0
    and t.id > c.id
)
select m.order_id,
       m.cancelled_id,
       m.cancelled_date,
       m.substitute_id,
       m.substitute_date
from cte_match m
where m.parent_order_id is not null
order by m.order_id,
         m.cancelled_id;


order_id
cancelled_id
cancelled_date
substitute_id
substitute_date


100000
2
2020-10-11
3
2020-10-12

100000
2
2020-10-11
4
2020-10-12

100001
5
2020-10-12
6
2020-10-13


-- version with dates and quantities
with cte_cancel as
(
  select t.order_id,
         t.id,
         t.cancelled_date,
         t.quantity
  from transactions t
  where t.cancelled = 1
),
cte_match as
(
  select c.order_id,
         c.id as cancelled_id,
         c.quantity as cancelled_qty,
         c.cancelled_date,
         t.id as substitute_id,
         t.quantity as substitute_qty,
         t.created_date as substitute_date,
         case when sum(t.quantity) over(partition by t.order_id
                                        order by t.id
                                        rows between unbounded preceding and current row) <= c.quantity
              then c.id end as parent_order_id
  from cte_cancel c
  join transactions t
    on t.order_id = c.order_id
  where t.cancelled = 0
    and t.id > c.id
)
select m.order_id,
       m.cancelled_id,
       m.cancelled_qty,
       m.cancelled_date,
       m.substitute_id,
       m.substitute_qty,
       m.substitute_date
from cte_match m
where m.parent_order_id is not null
order by m.order_id,
         m.cancelled_id;


order_id
cancelled_id
cancelled_qty
cancelled_date
substitute_id
substitute_qty
substitute_date


100000
2
200
2020-10-11
3
50
2020-10-12

100000
2
200
2020-10-11
4
150
2020-10-12

100001
5
300
2020-10-12
6
300
2020-10-13
order_id	id	quantity	cancelled_date	created_date	cancelled
100000	1	100	null	2020-10-10	False
100000	2	200	2020-10-11	2020-10-10	True
100000	3	50	null	2020-10-12	False
100000	4	150	null	2020-10-12	False
100001	5	300	2020-10-12	2020-10-11	True
100001	6	300	null	2020-10-13	False
100001	7	50	null	2020-10-14	False