title: SQL Server : running totals of two tables in CTE
body: <p>I'm having a bit of trouble creating a running total for two tables in a CTE. Due to the nature of the beast (two tables that I have to UNION ALL) I get double date rows.</p>
  
  <p>What's the best way to add them together?</p>
  
  <p>Here is a sample code. <code>Date</code> is <code>date</code>. <code>Cost</code> and <code>Total</code> are <code>decimal(20, 4)</code>.</p>
  
  <pre><code>with CTE_Blah
  As
  (
      SELECT
          [Date]
          ,SUM([Cost]) as [Cost_Sum]
          ,SUM([Total]) as [Invoiced_Sum]
      FROM TSL_Q40_DATED
      WHERE   ([Date] BETWEEN '2016-01-01' AND '2016-01-31') AND [Rego] &lt;&gt; ''
      GROUP BY [Date]
      UNION ALL
      SELECT
          [Date]
          ,SUM([Cost]) as [Cost_Sum]
          ,SUM([Total]) as [Invoiced_Sum]
      FROM TSL_Q41_DATED
      WHERE   ([Date] BETWEEN '2016-01-01' AND '2016-01-31') AND [Rego] &lt;&gt; ''
      GROUP BY [Date]
  )
  select
      [Date]
      ,SUM([Cost_Sum]) over(order by [Date] rows unbounded preceding) as [Cost_Sum_Total]
      ,SUM([Invoiced_Sum]) over(order by [Date] rows unbounded preceding) as [Invoiced_Sum_Total]
  from CTE_Blah
  </code></pre>
  
  <p>Data with headers:</p>
  
  <pre><code>Date    Cost_Sum_Total  Invoiced_Sum_Total
  2016-01-03  106.7500    2976.5300
  2016-01-03  2814.2184   6188.3800
  2016-01-04  2814.2184   6626.3800
  2016-01-05  25182.9184  69719.7260
  2016-01-05  62910.4085  121623.9760
  2016-01-06  76959.7885  169941.0178
  2016-01-06  111582.4872 219210.3828
  2016-01-07  130267.1772 276630.7298
  2016-01-07  157257.1861 319858.9298
  2016-01-08  181760.3061 374464.6628
  2016-01-08  216602.5447 422211.4528
  2016-01-09  216602.8647 422202.4528
  2016-01-11  232240.6247 475385.5239
  2016-01-11  292532.0367 555308.8015
  2016-01-12  308909.0367 610309.8151
  2016-01-12  352102.4805 667868.5984
  2016-01-13  379601.3205 735715.6454
  2016-01-13  443095.2996 819906.0354
  2016-01-14  485450.7396 924794.9284
  2016-01-14  570151.1295 1036974.5164
  2016-01-15  599628.5995 1133408.4675
  2016-01-15  682348.2252 1245186.5388
  2016-01-16  682570.0052 1245727.4688
  2016-01-16  683832.8852 1247475.2988
  2016-01-18  710836.7252 1326957.7818
  2016-01-18  773762.3701 1413407.1843
  2016-01-19  798178.4701 1487877.5098
  2016-01-19  855247.8828 1566829.2628
  2016-01-20  875797.9428 1625227.4776
  2016-01-20  919321.1908 1684962.4396
  2016-01-21  945941.7808 1764925.5599
  2016-01-21  991368.0220 1829901.9009
  2016-01-22  1026347.2920    1934196.0364
  2016-01-22  1125390.4969    2069489.9374
  2016-01-23  1143017.4887    2089926.9674
  2016-01-25  1167810.0887    2158779.0054
  2016-01-25  1218388.1937    2229148.9254
  2016-01-26  1265004.9037    2365723.2409
  2016-01-26  1331139.5559    2463935.7624
  2016-01-27  1374683.8859    2586818.1040
  2016-01-27  1538084.3589    2802247.8524
  2016-01-28  1602411.9689    2980781.7534
  2016-01-28  1743563.6906    3170026.1664
  2016-01-29  1836842.7906    3411791.7531
  2016-01-29  2052226.0474    3693064.0627
  2016-01-30  2060326.0474    3710266.4677
  2016-01-30  2068572.2762    3721389.5477
  2016-01-31  2068572.2762    3721422.8677
  2016-01-31  2068943.5162    3722678.2677
  </code></pre>