CODEWITHSUNDEEP
sql
Cat
Cat

Reputation:

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

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

Upvotes: 3

Views: 4301

Answers (4)

mwigdahl
mwigdahl

Reputation: 16588

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

Upvotes: 1

ʞɔıu
ʞɔıu

Reputation: 48456

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

Upvotes: 2

Rune Grimstad
Rune Grimstad

Reputation: 36340

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.

Upvotes: 0

Glen Solsberry
Glen Solsberry

Reputation: 12320

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

Upvotes: 0

Related Questions