How to group data by summed amount less than x

Question

Having an issue handling this in sql. Have a table of Check Amounts tied to customers. If I want to give them each unique numbers to be paid with, I cannot figure out how to do that with just an update statement and not going line by line(there is a large amount of data)

I have to group by a certain criteria, and send out payments, but they cannot be above $10000.

Table

customer CheckAmount
a   4000
a 5000
a 2001
b 3333
c 8000
d 11000

After my update it should look like :

customer checkamount paymentnumber
a 4000 1
a 5000 1
a 2001 2
b 3333 3
c 8000 4
d 11000 5

Is this possible in just an update statement ?

Answer 1

This problem can be solved if there is another column that identifies the transaction by time, like this:

transaction_num     customer    checkamount
1                   a           4000    
2                   a           5000    
3                   a           2001    
4                   b           3333    
5                   c           8000    
6                   d           11000

Then the update statement could be like this:

UPDATE p 
SET p.paymentnumber = agg.new_paymentnumber
FROM payments p
JOIN (
  SELECT *,
    DENSE_RANK() OVER (
      ORDER BY customer,
      -- Only the first payments are not higher than 10,000:
      /* CASE WHEN agg_sum <= 10000 THEN 0 ELSE agg_sum END */
      -- All payments are within 10,000:
      CEILING(agg_sum / 10000)
    ) AS new_paymentnumber
  FROM (
    SELECT *, SUM(checkamount) OVER (
      PARTITION BY customer ORDER BY transaction_num
    ) AS agg_sum
    FROM payments
  ) t 
) agg ON p.transaction_num = agg.transaction_num

fiddle