How to return a record when the sum reached a certain threshold

Question

Hello. I am using SQL and I have a table with three colums: account, transaction_date, Points. Each account will have multiple transaction_dates and Points earned for each transaction.

How do I return the transaction_date when each account reached a certain threshold (i.e. accumulatee 100 Points). Say the first account has 2000 transactions and the first five have each 21 Points. I would like the query to return transaction # 5 because that is when the account reached 100.

Can anybody help? Thanks! Cat

Answer 1

This should get you what you're after.

SELECT account_id, MIN(transaction_date) FROM table t1 WHERE (SELECT SUM(points) FROM table t2 WHERE t2.transaction_date < t1.transaction_date) <= 100 GROUP BY account_id

Answer 2

See this question:

http://stackoverflow.com/questions/287333/sql-select-until

It's not a complete answer but should point you in the right direction.

Answer 3

You could do this using a cursor in a stored procedure. Then you could just step through the records for the account and accumulate the points. As soon as you pass the threshold you return the current record.

But this will probably be quite slow if you have a large dataset, so if you know the threshold(s) you are after before running the query you could add an extra table where you tag the records where you cross the thresholds you are after. Updating this extra table could be done in a trigger on the transactions table.

Answer 4

select min(a.transaction_date), a.account from

(select sum(t1.points) as thesum, t2.transaction_date, t2.account 
from table t1
inner join table t2 on t1.account = t2.account and t1.transaction_date <= t2.transaction_date
group by t2.transaction_date, t2.account
having thesum >= 100) a 

group by a.account

Answer 5

Use a triangular join:

In T-SQL:

SELECT account, MIN(dt), MIN(points) 
FROM 
(
    SELECT t1.account, t1.date, sum(t2.points) AS points
    FROM table t1
      INNER JOIN table t2 ON t1.account = t2.account AND t1.dt >= t2.dt
    GROUP BY t1.account, t1.date
    HAVING SUM(t2.points) > 100
) iq
GROUP BY account