Calculate a running total in SQL Server 2012

running-total-sql-server-2012.sql

CREATE TABLE #TestData (
   id       int not null identity(1,1) primary key,
   account  varchar(10) not null,
   deposit  int not null
);
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 10)
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 20)
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 30)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 40)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 50)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 60)
INSERT INTO #TestData (account, deposit) VALUES ('Party', 70)
INSERT INTO #TestData (account, deposit) VALUES ('Party', 80)

--------------------------------------------------------------------
-- Using OVER clause
--------------------------------------------------------------------
SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (ORDER BY a.id) AS 'total'
FROM   #TestData a
ORDER BY a.id;

--------------------------------------------------------------------
-- Using OVER clause, partition
--------------------------------------------------------------------
SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total'
FROM   #TestData a
ORDER BY a.id;

--------------------------------------------------------------------
-- Using sub query
--------------------------------------------------------------------
SELECT a.*, 
    (SELECT SUM(b.deposit) FROM #TestData b WHERE b.id <= a.id) AS 'total'
FROM #TestData a

--------------------------------------------------------------------
-- Using CTE
--------------------------------------------------------------------
;WITH cte AS (
    SELECT id, account, deposit, deposit as 'total'
    FROM #TestData
    WHERE id = 1

    UNION ALL

    SELECT a.id, a.account, a.deposit, cte.total + a.deposit
    FROM cte JOIN #TestData a ON cte.id + 1 = a.id
)
SELECT * FROM cte 
OPTION (MAXRECURSION 32767);

DROP TABLE #TestData

i am working with your first option but.. when i want to filter data between dates it does not meet requirement because
it sum only the selected filtering dates it does not calculate previous sum but i need also sum from first entry...