Geek Answers Handbook

SQL Server Reverse Running Total

I have this table:

id  ID  Date    Cumulative
1   x   Jan-10  10
3   x   Feb-10  40
7   x   Apr-10  60
9   x   May-10  100
2   y   Jan-10  20
6   y   Mar-10  40
8   y   Apr-10  60
10  y   May-10  100

I need to reverse the “cumulative” MS SQL Server query as follows

id  ID  Date    Cumulative  Reversed
1   x   Jan-10  10  10
3   x   Feb-10  40  30
7   x   Apr-10  60  20
9   x   May-10  100 40
2   y   Jan-10  20  20
6   y   Mar-10  40  20
8   y   Apr-10  60  20
10  y   May-10  100 40

Note: The query is for SQL Server 2012.

+4
source share
2 answers

You can use lagto get the value in the previous line and subtract from the current value of the line to get the opposite value.

select t.*, cumulative - coalesce(lag(cumulative) over(partition by id order by date),0) as reversed
from tablename t

From the comment by @Gordon Linoff .. you can use lag(cumulative,1,0)instead coalesce.

select t.*, cumulative-lag(cumulative,1,0) over(partition by id order by date) as reversed
from tablename t
+7
source

For below Sql server 2012 using recursive CTE

declare @t table(id int,IDs varchar(20),Dates varchar(20),Cumulative int)
insert into @t values
(1,'x','Jan-10',  10)
,(3,'x','Feb-10',  40)
,(7,'x','Apr-10',  60)
,(9,'x','May-10', 100)
,(2,'y','Jan-10',  20)
,(6,'y','Mar-10',  40)
,(8,'y','Apr-10',  60)
,(10,'y','May-10',100)

;With CTE as
(select *,row_number()over(partition by ids order by id)rn 
from @t
)
,CTE1 as
(select id,ids,dates, Cumulative,rn,Cumulative Reversed 
from cte where rn=1
union all
select c.id,c.ids,c.Dates,c.Cumulative,c.rn 
,c.Cumulative-c1.Cumulative
from cte c
inner join cte c1 on c.ids=c1.ids
where c.rn=c1.rn+1
)
select * from cte1
+1
source

More articles:


All Articles