You could try something like this:

SQL

--consider cte as being specialized temp tables, each one being encapsulated within the parenthesis "()". --the cte starts off using the with clause, ensure all other statements are terminated using the ";" before using the with clause. --note some like to write ;with when using the with. with CombineTable as ( --setup dummy data --here is the table data in question --table_X data 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 --table_Y data 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 --table_Z data 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 ( --query the CombineTable cte above to create a new rowid column ordered by [Date] select row_number() over (order by [Date]) rowid, * from CombineTable ), CombineTableCalc as ( --now here comes the tricky bit with recursive cte --first part query the CombineTableRowID to get an anchor record --consider the anchor record to be the root (first parent) record the other records will feed off select ctrid.rowid, ctrid.[Date], ctrid.[Amount Type], ctrid.[Amount], --check to see if Amount should be added or subtracted --will be first record so the Amount will be added or subtracted to 0 case when [Amount Type] = 'Amount_X' then --add the Amount as Amount_X 0 + Amount else --minus the Amount as not Amount_X so will be either Amount_Y or Amount_Z 0 - Amount end Calculation from CombineTableRowID ctrid --this filter will get the first row for the anchor record where rowid = 1 --now here comes the recursive part union all select ctridC.rowid, ctridC.[Date], ctridC.[Amount Type], ctridC.[Amount], --add or subtract the Amount to the derived Calculation case when ctridC.[Amount Type] = 'Amount_X' then ctc.Calculation + ctridC.Amount else ctc.Calculation - ctridC.Amount end Calculation --querying the CombineTableRowID again --consider this to be the Child table from CombineTableRowID ctridC --make note the join table is in itself the same cte --this is where the recursion (magic) happens --consider this to be the Parent table inner join CombineTableCalc ctc --as you can see, here is where the match up occurs between the Parent and Child table on ctc.rowid = ctridC.rowid - 1 ) --and lastly query from the CombineTableCalc to get the recursive results 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[^]

You should be able to google that stuff. It's not worth 10 points for me to spend a few hours writing it!