SQL Expressions using result of previous row of UNION ALL
<p>I have these two tables:</p>

<pre><code>Collect: Date, Rute, Value_Collected...
Records: Date, Rute, Value, Type...
</code></pre>

<p>The other columns are not relevant in this case. So I have this working code:</p>

<pre><code>SELECT
Totals.Date                             AS [Date],
Sum(Totals.Collected)                   AS [Collect],
Sum(Totals.Injected)                    AS [Injections],
Sum(Totals.Spent)                       AS [Expenses],
([Collect] + [Injections]) - [Expenses] AS [Total Day]

FROM (
    SELECT
    Records.Date                        AS [Date],
    0                                   AS [Collected],
    Sum(IIF(Type =  3,Records.Value,0)) AS [Injected],
    Sum(IIF(Type &lt;&gt; 3,Records.Value,0)) AS [Spent]
    FROM Records GROUP BY Records.Date

    UNION ALL

    SELECT
    Collect.Date                 AS [Date],
    Sum(Collect.Value_Collected) AS [Collected],
    0                            AS [Injected],
    0                            AS [Spent]
    FROM Collect GROUP BY Collect.Date
) AS Totals GROUP BY Totals.Date
</code></pre>

<p>And that give me this result:</p>

<pre><code>|Date       |Collect    |Injections  |Expenses   |Total Day
-----------------------------------------------------------
|5/09/2016  |$47.000   |$0           |$0         |$47.000 |
|6/09/2016  |$20.000   |$0           |$0         |$20.000 |
|7/09/2016  |$13.000   |$0           |$0         |$13.000 |
|11/09/2016 |$122.000  |$0           |$0         |$122.000|
|14/09/2016 |$7.000    |$0           |$0         |$7.000  |
|16/09/2016 |$100.000  |$0           |$7.000     |$93.000 |
|30/09/2016 |$0        |$80.000      |$65.000    |$15.000 |
-----------------------------------------------------------
</code></pre>

<p>And that is ok for now, but the formula of column <code>[Total Day]</code> must be calculated like this <code>([Total Day Of Last Row] + [Collect] + [Injections]) - [Expenses]</code> instead of <code>([Collect] + [Injections]) - [Expenses]</code> to give me this desired result:</p>

<pre><code>|Date       |Collect      |Injections  |Expenses   |Total Day
-------------------------------------------------------------
|5/09/2016  |$47.000      |$0          |$0         |$47.000 |
|6/09/2016  |$20.000      |$0          |$0         |$67.000 |
|7/09/2016  |$13.000      |$0          |$0         |$80.000 |
|11/09/2016 |$122.000     |$0          |$0         |$202.000|
|14/09/2016 |$7.000       |$0          |$0         |$209.000|
|16/09/2016 |$100.000     |$0          |$7.000     |$302.000|
|30/09/2016 |$0           |$80.000     |$65.000    |$317.000|
-------------------------------------------------------------
</code></pre>

<p>But I dont know how to get the <code>[Total Day]</code> of the previous row for calculate the <code>[Total Day]</code> of current row. If I try to run a subquery over <code>[Totals]</code>, it gives me an error because it cannot find the table <code>Totals</code>. I think it is because it is not a "real" table, but really I don't know how to achive this. I really appreciate any help.</p>

<p>PS: the names of the columns and tables are originally in Spanish but translated for purposes so that you could understand what I need to accomplish.</p>