Esraa_92 Esraa_92 - 5 months ago 16
SQL Question

How can I make this query recursive Sql Server?

I have this table structure for Balances table:

enter image description here

And this is the view:

enter image description here

I have also this structure for Amounts table:

enter image description here

This is the view mode for Amounts table:

enter image description here

First of all I need to get the amount value for a specific day in Amounts Table:

enter image description here

with this query I get the amount 300 in date 07/07/2016.
Once achieved this figure, I need to make a recursive query with Balances table.
The end result should be like this:

Name abstractAmount addAmount Balance
----- -------------- --------- -------
Josep 100 400
Maria 50 350
George 60 410
Julianne 25 385


what is this? This result is achieved taking the 300 from the Amounts table, and for each row in Balance table I see:
If the abstracAmount in the first row is not empty, I make this mathematical calculation: balance = (300 - abstractAmount), in case is empty and the addAmount column has values I make this mathematical calculation balance = (300 + addAmount)
In the rest of rows I do the same but the calculation is not on 300, is on the last row balance:
For example:
In the first row the balance is 400 because the addamount has value so I make this calculation : 300 + 100 = 400
In the second row the balance is 350 because the abstractAmount is not empty so I take the balance value for the last row and make this calculation : 400 - 50 = 350.
And the same thing for the rest of rows, only the first row takes the balance value for the amounts table.


Notes:

1. Always the column abstractAmount subtracts values, and the addAmount column sum values.


  1. Always one of this columns (abstractAmount | addAmount) will be empty .

  2. Only the first row takes the value to make the mathematical calculation for the Amounts table, the rest of rows takes the value for the row before.




How can I get this final result? :

Name abstractAmount addAmount Balance
----- -------------- --------- -------
Josep 100 400
Maria 50 350
George 60 410
Julianne 25 385


I accept suggestions, thanks.

Answer

Instead of recursion, you can use window functions. More specifically a sum over rows unbounded preceding to get a running total (+ the start balance):

select *,300 +  sum(isnull(addAmount,0) - ISNULL(abstractAmount,0))  over (order by id rows unbounded preceding) Balance 
from Balances

The isnull(addAmount,0) - ISNULL(abstractAmount,0) is simply the mutation for every row. The over (order by id rows unbounded preceding) scopes the sum to the current row and all preceding rows according to id.

To get the base from the amounts table, you can have simply have the (select ...where date..) as a value instead of '300' or a bit more nifty: with a cross join to the amounts table:

select b.*, a.dateInsertion,a.amount, a.amount +  sum(isnull(addAmount,0) - ISNULL(abstractAmount,0))  over (order by b.id rows unbounded preceding) Balance 
from Balances b
cross join Amounts a
where a.dateInsertion = '20160707'

With the cross join without the where, you would get all possible balances