i trying add value of previous row current row column cumulative
select ddate date, etype, reference, linkacc contraacc, description, sum(case when amount > 0 amount else 0 end) debits, sum(case when amount < 0 amount else 0 end) credits, sum(amount) cumulative dbo.vw_lt accnumber ='8400000' , [ddate] between '2016-04-01 00:00:00' , '2016-04-30 00:00:00' , [datasource] = 'pas11cedcre17' group ddate, etype, reference, linkacc, description, amount output(what getting):
date reference contraacc description debits credits cumulative -------------------------------------------------------------------------- 2016-04-01 cc007 8000000 d/cc007 0 -39.19 -39.19 2016-04-01 cc007 8000000 d/cc007 1117.09 0 1117.09 2016-04-01 cc009 8000000 cc009 2600 0 2600 in cumulative column should below(what need):
date reference contraacc description debits credits cumulative -------------------------------------------------------------------------- 2016-04-01 cc007 8000000 d/cc007 0 -39.19 -39.19 2016-04-01 cc007 8000000 d/cc007 1117.09 0 1077.9 2016-04-01 cc009 8000000 cc009 2600 0 3677.9
before delve solution, let me tell if using sql server version more 2012, there lag , lead, can solve this.
i not giving exact query solve problem (as dont know primary key table is), can idea seeing below example
declare @t table ( accountnumber varchar(50) ,dt datetime ,transactedamt bigint ) insert @t values ('0001','7/20/2016',1000) insert @t values ('0001','7/21/2016',-1000) insert @t values ('0001','7/22/2016',2000) insert @t values ('0002','7/20/2016',500) insert @t values ('0002','7/21/2016',-500) insert @t values ('0002','7/22/2016',2000) ;with cte ( select row_number() over(partition accountnumber order dt) rn, * @t ),cte1 ( select *,transactedamt totalbalance cte rn = 1 union select t1.*,t1.transactedamt + t0.transactedamt totalbalance cte t1 join cte t0 on t1.accountnumber = t0.accountnumber , t1.rn = t0.rn+1 , t1.rn > 1 ) select * cte1 order accountnumber 
Comments
Post a Comment