Running total until specific condition is met

Question

I have a question about running totals, I know there are several approaches. However I have slight modification of it and I'm struggling to find the right way.

So I have orders, each order has its unique id and quantity. Quantity can go up (buying) and down (selling). Ids go in order but are not 'dense', so there might be a gap between two subsequent orders. I need to select all orders that represent the oldest position. Position is a continuous range of orders, overall sum of those is 0, example (id, qty): (1, 1) (2,2) (3,-3) (4,1) (5,-1) - there are two positions, the oldest position consists of orders with ids 1,2,3.

My current approach:

Schema

CREATE TABLE [dbo].[orders](
    [id] [int] NULL,
    [qty] [int] NULL
) ON [PRIMARY]

Solution

create table #or (id int, qty int, rn int)
create clustered index ix_orid on #or(rn)
insert into #or
select *, ROW_NUMBER() over(order by id) as rn from dbo.orders;

with position as (
select o1.*, o1.qty as s from #or o1 where rn = 1
union all
select o1.id, o1.qty, o1.rn, o1.qty + position.s   
from #or o1
join position
on o1.rn = position.rn + 1
where position.s > 0
)
select * from position 
option (maxrecursion 0)

drop table #or

My concern is that I create big temporary table, for all orders. And all time goes into creating this table. I have about ~3.000.000 rows in dbo.orders and ~15.000 is the result set. My current idea is to limit insert into #or using top hint with reasonable amount, and run it several times until we are done. But I wonder if it can be done easier?

Answer 1

As suggested in comments, I can get the latest id of the order in a single position by watching running total goes to zero. Then I can just select orders in range

declare @latestId int;
WITH T1
     AS (SELECT *,
                SUM(qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS total
         FROM   [dbo].[orders])
SELECT top (1) @latestId = id
FROM   T1
WHERE  total <= 0
order by id

select * from [dbo].[orders] where 1 <= id and id <= @latestId