SQL Server 2022

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

select @@version;


(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)



create table RECORDS
(RECORD_ID      int
,SECURITIES_ID  char(1)
,DEBITCREDIT    char(1)
,NOMINAL_VALUE  int
,BOOKING_ID     int
);
insert into RECORDS values (1,'A','C',100,10), ( 2,'B','D',100, 20),
                           (3,'B','C',100,30), ( 4,'B','D',200, 40), 
                           (5,'C','D', 50,50), ( 6,'B','D',  1, 60),
                           (7,'B','D', 50,70), ( 8,'B','D', 20, 80),
                           (9,'B','D', 30,90), (10,'B','D',100,100);
10 rows affected

with 
  candidates as
  (
    select *
    from records
    where securities_id = 'B'
    and debitcredit = 'D'
  ),
  series (total, ids, last_id) as
  (
    select nominal_value, cast(',' + cast(record_id as varchar(20)) + ',' as varchar(1000)), record_id
    from candidates
    union all
    select 
      s.total + c.nominal_value,
      cast(s.ids + cast(c.record_id as varchar(20)) + ',' as varchar(1000)),
      c.record_id
    from series s
    join candidates c on c.record_id > s.last_id
    where s.total + c.nominal_value <= 300
  ),
  matches as
  (
    select *
    from series
    where total = 300
  )
select dense_rank() over (order by m.ids) as grp, r.*
from records r
join matches m on m.ids like '%,' + cast(r.record_id as varchar(20)) + ',%'
order by grp, r.record_id;


grp
RECORD_ID
SECURITIES_ID
DEBITCREDIT
NOMINAL_VALUE
BOOKING_ID


1
2
B
D
100
20

1
4
B
D
200
40

2
2
B
D
100
20

2
7
B
D
50
70

2
8
B
D
20
80

2
9
B
D
30
90

2
10
B
D
100
100

3
4
B
D
200
40

3
10
B
D
100
100

4
4
B
D
200
40

4
7
B
D
50
70

4
8
B
D
20
80

4
9
B
D
30
90

grp	RECORD_ID	SECURITIES_ID	DEBITCREDIT	NOMINAL_VALUE	BOOKING_ID
1	2	B	D	100	20
1	4	B	D	200	40
2	2	B	D	100	20
2	7	B	D	50	70
2	8	B	D	20	80
2	9	B	D	30	90
2	10	B	D	100	100
3	4	B	D	200	40
3	10	B	D	100	100
4	4	B	D	200	40
4	7	B	D	50	70
4	8	B	D	20	80
4	9	B	D	30	90

grp	RECORD_ID	SECURITIES_ID	DEBITCREDIT	NOMINAL_VALUE	BOOKING_ID
1	2	B	D	100	20
1	4	B	D	200	40
2	2	B	D	100	20
2	7	B	D	50	70
2	8	B	D	20	80
2	9	B	D	30	90
2	10	B	D	100	100
3	4	B	D	200	40
3	10	B	D	100	100
4	4	B	D	200	40
4	7	B	D	50	70
4	8	B	D	20	80
4	9	B	D	30	90

grp	RECORD_ID	SECURITIES_ID	DEBITCREDIT	NOMINAL_VALUE	BOOKING_ID
1	2	B	D	100	20
1	4	B	D	200	40
2	2	B	D	100	20
2	7	B	D	50	70
2	8	B	D	20	80
2	9	B	D	30	90
2	10	B	D	100	100
3	4	B	D	200	40
3	10	B	D	100	100
4	4	B	D	200	40
4	7	B	D	50	70
4	8	B	D	20	80
4	9	B	D	30	90