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:
CREATE TABLE [dbo].[orders]( [id] [int] NULL, [qty] [int] NULL ) ON [PRIMARY]
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?