Select From Multiple Tables Multiple Columns In One Column and Calculate an Amount using the previous row in SQL SERVER

--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]
;