Sum with previous row values in t-sql.

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	            

August 26th, 2015 4:01pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics