Get Running Total with CTE

 

Declare @Table table (id int identity(1,1), amount numeric(12,2), run_total numeric(12,2))
Insert into @Table(amount) Values( 1000)
Insert into @Table(amount) Values( 2000)
Insert into @Table(amount) Values(4000)
Insert into @Table(amount) Values(4000)
Insert into @Table(amount) Values(6000)
Insert into @Table(amount) Values(3000)


;With CTE_Tbl(id, amount, run_total)
As
(
    select id, amount, amount as run_total 
        from @Table t where id = 1
    union all 
    select t1.id, t1.amount, cast (t1.amount + c.run_total as numeric(12,2)) as rt 
     from @Table t1 
     join CTE_Tbl c on c.id+1 = t1.id    
)
select * from CTE_Tbl