Issue Calculating from Rows and Columns(Summing two columns with the third of a different row)

Question

With reference to my previous question Adding columns resulting from GROUP BY clause

SELECT AcctId,Date,
   Sum(CASE
         WHEN DC = 'C' THEN TrnAmt
         ELSE 0
       END) AS C,
   Sum(CASE
         WHEN DC = 'D' THEN TrnAmt
         ELSE 0
       END) AS D
FROM   Table1 where AcctId = '51'
GROUP  BY AcctId,Date
ORDER  BY AcctId,Date

I executed the above query and got my desired result..

  AcctId       Date         C        D
    51       2012-12-04   15000      0
    51       2012-12-05   150000  160596
    51       2012-12-06    600        0

now I have a another operation to do on the same query i.e.

I need the result to be like this

  AcctId   Date                                Result

    51       2012-12-04    (15000-0)->       15000  
    51       2012-12-05   (150000-160596) + (15000->The first value)  4404
    51       2012-12-06     600-0         +(4404 ->The calculated 2nd value) 5004

Is it possible with the same query??.

If this were an Oracle question, this could be solved easily using the [`MODEL` clause](http://stackoverflow.com/a/12245888/521799). `LAG()` is probably not going to work in this case... — Lukas Eder, Dec 16 '12 at 10:10
If this was a SQL Server 2012 question, you could probably use `SUM(...) OVER (ORDER BY ...)`. — Andriy M, Dec 16 '12 at 10:38
@AndriyM: So the windowing clauses were finally added to T-SQL window functions? — Lukas Eder, Dec 16 '12 at 10:41
@LukasEder: [Yes](http://msdn.microsoft.com/en-us/library/ms189461.aspx "OVER Clause (Transact-SQL)"), if you were asking specifically about support of `ORDER BY` in *aggregate* window functions (as opposed to *ranking* window functions, which have supported (in fact, required) ORDER BY since their introduction in SQL Server 2005). — Andriy M, Dec 16 '12 at 10:51
I'm using SQL Server 2008 R2 , is it possible ? Please provide with an Example. — tsvsdev, Dec 16 '12 at 11:08
@vstsdev - The name for the type of the query you are doing is running totals. This is a pain to do in < SQL Server 2012 and best done in the application. If you must do it in SQL the following answwers may help. [One](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver), [Two](http://stackoverflow.com/questions/7357516/subquery-or-leftjoin-with-group-by-which-one-is-faster/7454564#7454564) — Martin Smith, Dec 16 '12 at 11:37

Aleksandr Fedorenko · Accepted Answer · 2012-12-16T17:06:32.573

use recursive CTE

;WITH cte AS
 (
  SELECT AcctId, Date,
     Sum(CASE
           WHEN DC = 'C' THEN TrnAmt
           ELSE 0
         END) AS C,
     Sum(CASE
           WHEN DC = 'D' THEN TrnAmt
           ELSE 0
         END) AS D,
     ROW_NUMBER() OVER (ORDER BY AcctId, Date) AS Id 
  FROM   Table1 where AcctId = '51'
  GROUP  BY AcctId, Date
  ), cte2 AS
 (
  SELECT Id, AcctId, Date, C, D, (C - 0) AS Result
  FROM cte
  WHERE Id = 1
  UNION ALL
  SELECT c.Id, c.AcctId, c.Date, c.C, c.D, (c.C - c.D) + ct.Result
  FROM cte c JOIN cte2 ct ON c.Id = ct.Id + 1
  )
  SELECT *
  FROM cte2

Simple example on SQLFiddle

Issue Calculating from Rows and Columns(Summing two columns with the third of a different row)

1 Answers1

Linked