I'm trying to calculate the running total of a table whose initial total value can only be 0.create table #prod (CustomerID int not null,Pos int not null,Total int not null default 0)insert #prodselect distinct c.id as "CustomerID", Pos = 1, Total = 0 from demographics d inner join persons c on d.id = c.idwhere d.code in ("X","Y")and d.type = "foo" and d.begindate >= "01-Jan-2007"insert #prodselect distinct c.id as "CustomerID", Pos = 2, Total = (select count(CustomerID) from #prod where Pos = 1)from demographics d inner join persons c on d.id = c.id inner join delivered m on m.id = c.idwhere d.code in ("X","Y")and d.type = "foo" and m.yr >= 2007I'd like to see the results aligned to their position (pos), rather than offset, as here:Total Pos----------- -----------0 13739 2
Pos 1 should have 3739 since that is the total of that select block, Pos 2 should have its quantity, unseen here. How to alter or update the table to reflect the desired result set?Total Pos----------- -----------3739 15343 2
Running totals
Posted - 2010-11-04 : 17:26:34
