15,938,218 members
See more:
Hello !

I will make it simple : )

I have :

- 3 Tables
- Each Table has Columns (DATE) and (AMOUNT)

Table X

Date | Amount_X
01/01/2015 | 3000

Table Y

Date | Amount_Y
02/01/2015 | 1000

Table Z

Date | Amount_Z
03/01/2015 | 1000

I need to get this result with 4 Columns :

Date | Amount Type | Amount | Calculation

01/01/2015 | Amount_X | 3000 | 3000 ( = 0 + 3000 )
02/01/2015 | Amount_Y | 1000 | 2000 ( = 3000 - 1000 )
03/01/2015 | Amount_Z | 1000 | 1000 ( = 2000 - 1000 )

And So on ...

So :
If we have Amount_X We add ( + )
If we have Amount_Y Or Amount_Z We substract ( - )
So we have to get the value of the previous row in Column (Calculation). There is no previous row for the first row so The value is Null (0).

( NB : I think I will use CROSS APLY and ROW_NUMBER But I do not know how to hundle it. But it's up to you to give me the best solution )

I will improuve my question if needed

Thank You : )
Posted
Updated 9-Sep-15 9:03am
v2
jgakenhe 9-Sep-15 15:38pm
I would use PIVOT to get the first 4 columns as a subquery, then in the outer query, I'd use LAG.

You should be able to google that stuff. It's not worth 10 points for me to spend a few hours writing it!
j snooze 9-Sep-15 17:54pm
Why not just use the row number and a while loop in sql. Keep the totals in a variable and update each line to the calculation. I prefer while loops to cursors.
Member 11972042 10-Sep-15 14:51pm
Hi .. I think looping and updating the total in variable all the time makes it very slow .. I have thousands of rows in each table ..

## Solution 1

One way of doing it, is by using recursive common table expressions (cte).

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
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[^]

v3
Member 11972042 10-Sep-15 14:43pm
Hello !

Thank you for your Solution .. But I feel lost in your code .. It seems somthing advanced in SQL.

Could you please comment your code because I never used the common table expressions.

Thank you again : )
jaket-cp 11-Sep-15 3:21am
okay I will try :)