jonooo - 4 months ago 10x

SQL Question

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.

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

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
```

Source (Stackoverflow)

Comments