Is it possible to organize a selection in the request so that the total does not exceed a given number?

Question

SQL

There is a table with counterparties and the debt that arose before them with the time of its occurrence.
| contr (contractor) | time (date) | val (sum) |
You need to get a list of debts for an amount that is not more than a certain amount ready to pay at a time (max_cash). And also, the number of unique counterparties should not be more than 100 firms, and it is also impossible to make payments purely for one counterparty at a time, because. close by date (FIFO).
How can you organize a selection in a query so that the total does not exceed the max_cash number?

Answer 1

SELECT model, COUNT(model) AS Qty_model, 
       AVG(price) AS Avg_price
    FROM PC
    GROUP BY model
    HAVING AVG(price) < 800;

www.sql-tutorial.ru/ru/book_having_clause.html

Answer 2

Simply query all records sorted by due date and select one at a time until you reach the limit.

Answer 3

SELECT * FROM (
  SELECT contr, [time], SUM(val) OVER ( ORDER BY [time] ) AS sumcash
    FROM something ) AS cash
  WHERE sumcash <= max_cash

SELECT is an OVER clause .

Answer 4

You can try like this:

select d1.[time], d1.contr, d1.val
  from debt d1
  join debt d2
    on d2.[time] <= d1.[time]
 group by d1.contr, d1.val, d1.[time]
having sum(d2.val) <= 25 -- сумма которую нужно выплатить
 order by d1.[time]

Here debt is a table with debts.
There are a couple of points with this implementation:
1. You need to be sure that all debts will fall into the system with different dates and times,
if for example the date and time coincide for two, then how to build a FIFO question here.
2. It is advisable to somehow mark the debts that are paid, so as not to re-select them.
3. It is better to name the time field differently, now it coincides with the system data type for storing time.