You could try something like this:

```
--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!