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 |