SQL How to sum values of the same column until a threshold/condition is met?

Question

Edit - I am using MSSQL Server 2005 - SP4 (9.0.5000)

I have 3 columns in a table - Customer ID, Order Number, Payment Date, and Payment. When a user makes a payment, it logs data using the Customer ID, increments the order number (so the first payment is 1, second payment is 2, etc), and has a date and payment amount.

I only want to query all the first payments made until the total payment amount reaches a threshold/condition of 100.

First I tried just working with sum() to see if something would just magically pop into my head - but total sums are all I know how to do:

select CustomerID, Order, sum(Payment) as FirstFullPayment
from #temp
group by CustomerID

Then I tried making 2 different temporary tables - #up for unfinished payments and #fp for finished payments. My thinking is that I can put the confirmed >100 payments into #fp, and put the confirmed unfinished payments into #up with something like this:

select * into #fp
from #temp
where Order = 1 and Payment >= 100

select * into #up
from #temp
where Order = 1 and Payment < 100

Then I could sequentially join #up and #temp where Order = 2, 3, 4, etc., add the Payments in #up until they are >= 100, then insert them into #fp

The only thing is I know this is a very bad and roundabout method, and there must be a better and simpler way to do this! Thanks in advance!

Answer 1

You can use running sums and select the row you by your criteria: <=100

select top 1 * from 
    (
    select CustomerID,  
                (
                SELECT SUM(b.payment)
                           FROM #temp b
                           WHERE a.customerid=b.customerID and  b.[order] <= a.[order]) as FirstFullPayment
    from #temp a
    --where customerid=yourCustomerId
    )runningsums
where runningsums.FirstFullPayment<=100
order by runningsums.FirstFullPayment desc