Give this a go.
Should work for anything passed 2005.
CREATE TABLE #TEMP_STD_SUM ( ROW_ID INT IDENTITY(1,1) PRIMARY KEY , [DAY] DATE, STUDENT_ID INT , CLASS_ID INT , BEGIN_CNT INT NOT NULL DEFAULT(0) , SUBSCRIPTION_CNT INT NOT NULL DEFAULT(0) , CANCEL_CNT INT NOT NULL DEFAULT(0) , ACTIVE_TODAY_CNT INT NOT NULL DEFAULT(0) ) INSERT INTO #TEMP_STD_SUM ( [DAY] , STUDENT_ID , CLASS_ID , BEGIN_CNT , SUBSCRIPTION_CNT , CANCEL_CNT ) VALUES('2015-07-01', 4,3,201,1,1) INSERT INTO #TEMP_STD_SUM ( [DAY] , STUDENT_ID , CLASS_ID , SUBSCRIPTION_CNT , CANCEL_CNT ) VALUES('2015-07-02', 4,3,2,3),('2015-07-03', 4,3,56,32),('2015-07-04', 4,3,12,54) ;WITH base AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Student_ID ORDER BY Day) AS seq FROM #TEMP_STD_SUM ), rCTE AS ( SELECT ROW_ID, DAY, STUDENT_ID, CLASS_ID, BEGIN_CNT, SUBSCRIPTION_CNT, CANCEL_CNT, ACTIVE_TODAY_CNT, seq FROM base WHERE seq = 1 UNION ALL SELECT a.ROW_ID, a.DAY, a.STUDENT_ID, a.CLASS_ID, r.BEGIN_CNT +(r.SUBSCRIPTION_CNT - r.CANCEL_CNT) AS BEGIN_CNT, a.SUBSCRIPTION_CNT, a.CANCEL_CNT, a.ACTIVE_TODAY_CNT, a.seq FROM rCTE r INNER JOIN base a ON r.STUDENT_ID = a.STUDENT_ID AND r.seq + 1 = a.seq ) SELECT * FROM rCTE DROP TABLE #TEMP_STD_SUM