Click here to Skip to main content
14,690,651 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 10:03am
v2
Comments
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 ..

1 solution

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

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[^]
   
v3
Comments
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 :)
have another read, I have updated solution with comments.
hopefully it should be more clearer :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900