How to return date of reaching a certain threshold-CodePudding

Using SQL Server Management Studio.

Let's say I have a table with transactions that contains User, Date, Transaction amount. I want a query that will return the date when a certain amount is reached - let's say 100.

For example the same user performs 10 transactions for 10 EUR. I want the query to select the date of the last transaction because that's when his volume reached 100. Of course, once 100 is reached, the query shouldn't change the date with the latest transaction anymore, but leave it at when 100 was reached.

CodePudding user response：

case when sum(amt) over (partition by user order by date) - amt <  100
      and sum(amt) over (partition by user order by date)       >= 100 
    then 1 else 0 end

CodePudding user response：

Wrote this on pgadmin but I think syntax should be the same.

with cumulative as
(
select customer_id,
    sum(amount) over (partition by customer_id order by payment_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum_amt,
    payment_date
from payment
)
select customer_id
    , min(payment_date) as threshold_reached
from cumulative
where cum_amt>=100
group by customer_id