Get a running total (cumulative total) in SQL Server

This is available from SQL Server 2012.

–drop table #TestTable

create table #TestTable
(
Id int identity(1,1),
SaleType nvarchar(30),
SaleDate datetime,
SaleUnits int,
RunningSaleUnits int
)

insert #TestTable (SaleType,SaleDate,SaleUnits)
select N’Sale’,N’2013-10-20 11:49:11.787′,10
union
select N’Sale’,N’2013-10-21 11:49:11.787′,4
union
select N’Refund’,N’2013-10-22 11:49:11.787′,-5
union
select N’Sale’,N’2013-10-23 11:49:11.787′,6
union
select N’Sale’,N’2013-10-24 11:49:11.787′,5

select * from #TestTable order by SaleDate

update    t
set        RunningSaleUnits = Runningtotal
from    #TestTable t
join
(
select id,
sum(SaleUnits) over(order by SaleDate rows unbounded preceding) as Runningtotal
from #TestTable
) r
on t.Id = r.Id

select * from #TestTable order by SaleDate