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
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
where Order = 1 and Payment >= 100
select * into #up
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!