One way of doing it, is by using recursive common table expressions (cte).
You could try something like this:
with CombineTable as (
select
convert(datetime, '20150101',121) [Date],
'Amount_X' [Amount Type],
3000 Amount
union all select convert(datetime, '20150115',121), 'Amount_X', 5000
union all select convert(datetime, '20150109',121), 'Amount_X', 6000
union all select convert(datetime, '20150110',121), 'Amount_X', 7000
union all select convert(datetime, '20150104',121), 'Amount_X', 8000
union all select convert(datetime, '20150102',121), 'Amount_Y', 1000
union all select convert(datetime, '20150106',121), 'Amount_Y', 3000
union all select convert(datetime, '20150107',121), 'Amount_Y', 2000
union all select convert(datetime, '20150103',121), 'Amount_Z', 1000
union all select convert(datetime, '20150121',121), 'Amount_Z', 9000
union all select convert(datetime, '20150115',121), 'Amount_Z', 3000
), CombineTableRowID as (
select
row_number() over (order by [Date]) rowid,
*
from CombineTable
), CombineTableCalc as (
select
ctrid.rowid,
ctrid.[Date],
ctrid.[Amount Type],
ctrid.[Amount],
case
when [Amount Type] = 'Amount_X' then
0 + Amount
else
0 - Amount
end
Calculation
from CombineTableRowID ctrid
where rowid = 1
union all
select
ctridC.rowid,
ctridC.[Date],
ctridC.[Amount Type],
ctridC.[Amount],
case
when ctridC.[Amount Type] = 'Amount_X' then
ctc.Calculation + ctridC.Amount
else
ctc.Calculation - ctridC.Amount
end
Calculation
from CombineTableRowID ctridC
inner join CombineTableCalc ctc
on ctc.rowid = ctridC.rowid - 1
)
select *
from CombineTableCalc
order by [Date]
;
Hope that helps out :)
Here are a few links, which may help:
Fibonacci sequence using SQL Server CTE:
Fibonacci sequence using SQL Server CTE [
^]
Using Common Table Expressions:
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx[
^]
OVER Clause (Transact-SQL):
https://msdn.microsoft.com/en-gb/library/ms189461.aspx[
^]
CASE (Transact-SQL):
https://msdn.microsoft.com/en-us/library/ms181765.aspx[
^]