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);
6 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
  ),
  one_match as
  (
    select top 1 *
    from series
    where total = 300
  )
select *
from records r
where exists
(
  select null
  from one_match m
  where m.ids like '%,' + cast(r.record_id as varchar(20)) + ',%'
)
order by record_id;


RECORD_ID
SECURITIES_ID
DEBITCREDIT
NOMINAL_VALUE
BOOKING_ID


2
B
D
100
20

4
B
D
200
40