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 |