sybase sql: choose records for which sum of values in column XYZ equals some specific value - applied to subsets

Question

Using Sybase SQL (DBMS: Adaptive Server Enterprise/16.0.), I want to find the records fulffilling certain simple requirements (such as DEBITCREDIT == 'D') AND the following requirement: the sum of the nominal values of these records must equal 300.

Here is a screenshot as an example:

In the screenhot the purpose of my select statement would be to select the RECORD_IDs highlighted in yellow, namely RECORD_ID 2 and RECORD_ID 4. (Please note that NOMINAL_VALUE of RECORD_ID 2 + NOMINAL_VALUE of RECORD_ID 4 = 300.)

The following code would achieve this purpose, if it wasn't for RECORD 6:

create table RECORDS
(RECORD_ID      int
,SECURITIES_ID  char(1)
,DEBITCREDIT    char(1)
,NOMINAL_VALUE  int
,BOOKING_ID     int
)
go

insert RECORDS values (1,'A','C',100,10)
insert RECORDS values (2,'B','D',100,20)
insert RECORDS values (3,'B','C',100,30)
insert RECORDS values (4,'B','D',200,40)  
insert RECORDS values (5,'C','D', 50,50)
insert RECORDS values (6,'B','D',  1,60)
go


select RECORD_ID,
       BOOKING_ID
from   RECORDS
where  SECURITIES_ID = 'B'
and    DEBITCREDIT   = 'D'
group by SECURITIES_ID, 
         DEBITCREDIT
having sum(NOMINAL_VALUE) = 300
order by 1,2

go

Because of RECORD 6, sum(NOMINAL_VALUE) of all entries with (SECURITIES_ID = 'B' and DEBITCREDIT = 'D') is 301 (rather than 300).

How can I select the entries, for which all of the following is true (even if these conditions are true for a subset only): SECURITIES_ID = 'B', DEBITCREDIT = 'D' and sum(NOMINAL_VALUE) = 300

P.S.: this question is a follow-up question to the folowing question: sybase sql: choose records for which sum of values in column XYZ equals some specific value

P.P.S.: If there are several combinations available, which fulfill the criteria, then the query should simply choose one of those. Which one, doesn't matter. ... Here is an example:

In this example, the query should select either Records 2 and 4 or Records 4 and 6. Which combination (2/4 or 4/6) is selected, doesn't matter.

Answer 1

NOTE: while Sybase SQLAnywhere and Sybase IQ have a more robust SQL dialect (eg, CTEs, windows functions), Sybase ASE's SQL dialect is rather old/outdated/limited (thanks to SAP effectively killing off ASE development over the last 13 years)

Without support for (recursive) CTEs (eg, see Thorsten's answer) we're left with a couple options:

• write a query with an assumed max number of self-joins (gets quite unwieldly for anything beyond 3-5 self-joins; see FriendlyDragon's comment about 1K * 1K = 1M variants)
• write a batch process (or stored proc) that uses a looping construct to perform (tail) recursive joins (plus some means of deciding when to 'quit' recursing)

One idea based on an assumption that we'll find the desired sum within a max of 3 self-joins:

declare @total int
select  @total = 300

select  r1.RECORD_ID,
        r2.RECORD_ID,
        r3.RECORD_ID

from    RECORDS r1

left
join    RECORDS r2
on      r1.RECORD_ID     < r2.RECORD_ID
and     r1.SECURITIES_ID = r2.SECURITIES_ID
and     r1.DEBITCREDIT   = r2.DEBITCREDIT
and     @total           > r1.NOMINAL_VALUE

left
join    RECORDS r3
on      r2.RECORD_ID     < r3.RECORD_ID
and     r2.SECURITIES_ID = r3.SECURITIES_ID
and     r2.DEBITCREDIT   = r3.DEBITCREDIT
and     @total           > (r1.NOMINAL_VALUE + r2.NOMINAL_VALUE)

where   r1.SECURITIES_ID = 'B'
and     r1.DEBITCREDIT   = 'D'

and     r1.NOMINAL_VALUE + isnull(r2.NOMINAL_VALUE,0) + isnull(r3.NOMINAL_VALUE,0) = @total

NOTE: to limit output to one row either a) add top 1 to the query or b) run set rowcount 1 before running the query (and set rowcount 0 after the query)

Using OP's most recent updated table data:

insert RECORDS values (1,'A','C',100,10)
insert RECORDS values (2,'B','D',100,20)
insert RECORDS values (3,'B','C',100,30)
insert RECORDS values (4,'B','D',200,40)
insert RECORDS values (5,'C','D', 50,50)
insert RECORDS values (6,'B','D',100,60)

For @total = 100:

 RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2        NULL        NULL
           6        NULL        NULL

For @total = 200:

 RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2           6        NULL
           4        NULL        NULL

For @total = 300:

 RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2           4        NULL
           4           6        NULL

For @total = 400:

 RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2           4           6

For @total = 700:

 RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
 -- no results --

NOTE: results generated with ASE 16.0 SP04 PL04

Answer 2

This shold be done using a window function, in a subquery

select RECORD_ID,
       BOOKING_ID FROM 
 (
select RECORD_ID,
       BOOKING_ID,
  SUM(NOMINAL_VALUE) OVER(ORDER BY RECORD_ID) sumes
from   RECORDS
where  SECURITIES_ID = 'B'
and    DEBITCREDIT   = 'D') CTE WHERE sumes >= 300

RECORD_ID	BOOKING_ID
4	40
6	60

Answer 3

Sorry for answer instead of comments, but I want to put some tables here... maybe this answer make sence to delete after discussion...

Ok, you want to know, how you can agregate amount, and your example is very clear with one extra line.

id	SECURITIES_ID	DEBITCREDIT	NOMINAL_VALUE
2	B	D	100
4	B	D	200
6	B	D	1

But, let's try imaging, which row will be extra in this example?

id	SECURITIES_ID	DEBITCREDIT	NOMINAL_VALUE
2	B	D	100,00
4	B	D	100,00
6	B	D	100,00
9	B	D	100,00

As result, looks like you have wrong goal - you are trying to divide one whole number to separate smaller, but you don't know how many can be these separate parts...

For some reason it will be enought to some window function? But this is not very applicable for WHERE/HAVING part, am I right?

Answer 4

Use a recursive query to get all possible row combinations (1, 2, 3, 4, 5, 6, 1-2, 1-3, 1-4, 1-5, 1-6, 2-3, 2-4, 2-5, 2-6, 3-4, 3-5, 3-6, 4-5, 4-6, 5-6, 1-2-3, 1-2-4, ...). Well, you can stop adding rows of course once you hit a total of 300.

I haven't found any online tool for trying Sybase queries, so here is one for SQL Server that may need some little adjustments. I am collecting combinations here, remembering the participating IDs in a string starting an ending with a comma, e.g. ',1,3,5,'. Thus I can later look for an ID in the string, e.g. looking for ID 5 with where ',1,3,5,' LIKE '%,5,%'.

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;

Demo: https://dbfiddle.uk/ScRGartF

Here is another demo, where I select all matches: https://dbfiddle.uk/jdxIsGep