CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2business-intelligence
user3408417
user3408417

Reputation: 11

Get records when Running sum reaches target value

DECLARE @example TABLE(ID INT, Amount float)
INSERT INTO @example VALUES(1,100), (2,500), (3,50), (4,200)

select * from @example

DECLARE @Target Float = 600

Now, I need top records where Sum(Amount) = @Target, this target may vary.

Can some one please give me a sql query for this.

Upvotes: 0

Views: 358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can calculate the cumulative sum using a correlated subquery (as well as other methods). Assuming that the id uniquely identifies rows:

select e.*
from (select e.*,
             (select sum(amount)
              from @example e2
              where e2.id <= e.id
             ) as cumamount
      from @example e
     ) e
where cumamount = @Target;

This looks for the target value exactly. More commonly, you want something like:

where cumamount >= @Target and cumamount - amount < @Target;

That is, the first value that meets or exceeds the @Target value.

Upvotes: 1

Related Questions