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

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

From stackoverflow

• 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

• 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.

• 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.

  Joel Coehoorn : No need for cursor or extra table here.

  Rune Grimstad : I just saw your post. Nice one!

• 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
  

• 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