Rusty Divine

Live, Love, Learn, Teach

Calculating Running Totals using TSQL in SQL Server

The fastest method I’ve found of calculating running totals in tsql is to use a temporary table.

--Create a temporary variable
declare @RunningTotal money
set @RunningTotal = 0

--Setup a results table
declare @results table(RowId int, CustomerId int, TransactionDate date, TransactionType varchar(50), 
    Reference varchar(50), Amount money, DaysSince int, RemainingAmount money, RunningTotal money default 0)

--Insert everything except the running total
insert into @results(RowId, CustomerId, TransactionDate, TransactionType, Reference, Amount, DaysSince, RemainingAmount)
SELECT  
    row_number() over (order by art.Date, ardt.Name),
    art.CustomerId,   
    art.Date, 
    ardt.Name AS [Transaction Type], 
    case IsNull(art.Reference,'') when '' then art.DocumentNumber else art.Reference end as Reference, 
    art.Amount,
    datediff(d, art.date, getdate()) daysDiff,
    art.RemainingAmount
FROM         
    dbo.ARTransactions AS art INNER JOIN
    dbo.ARDocumentTypes AS ardt ON art.ARDocumentTypeId = ardt.ARDocumentTypeId
WHERE     
    (art.CustomerId = @CustomerId)
    and (art.RemainingAmount <> 0 or datediff(d, art.date, @StatementDate) < 30)
order by
    art.Date, 
    ardt.Name

--Now, update the running total using our temp variable
update @results
set @RunningTotal = RunningTotal = @RunningTotal + RemainingAmount

--Return the results
select * from @results order by RowId
blog comments powered by Disqus