sql - How to maintain a running balance in a month wise report -

select *      (select          year (dateoftransaction) year,         left(datename(month, dateoftransaction), 3) month,         sum(case when transtypename 'credit%' amount else 0 end) -              sum(case when transtypename 'debit%' amount else 0 end) balance                .............) t pivot (sum(balance) month in (jan, feb, march, ...., dec)) pvt 

this query returns month-wise report account balance. want result running balance.

example:

january month credit 5000, february month credit 2000

my query result

 year  jan   feb march...dec  2014  5000  2000 null ..null 

i want result this:

 year  jan   feb     march...dec  2014  5000 7000     null ..null             (5000+2000)     

try this

select year,jan = jan, feb = isnull(jan,0)+isnull(feb,0),....     (select          year (dateoftransaction) year,         left(datename(month, dateoftransaction), 3) month,         sum(case when transtypename 'credit%' amount else 0 end) -              sum(case when transtypename 'debit%' amount else 0 end) balance                .............) t pivot (sum(balance) month in (jan, feb, march, ...., dec)) pvt)t 

or can add temp table stores numbers 1 12

inner join #temp on n>=datepart(mm,dateoftransaction) group year(transaction), n