Hello ,

I have this table with the data.

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)   

 SELECT * FROM #TEMP_STD_SUM

 

ROW_ID	DAY	STUDENT_ID	CLASS_ID	BEGIN_CNT	SUBSCRIPTION_CNT	CANCEL_CNT	ACTIVE_TODAY_CNT
1	7/1/2015	4	3	201	1	1	0
2	7/2/2015	4	3	0	2	3	0
3	7/3/2015	4	3	0	56	32	0
4	7/4/2015	4	3	0	12	54	0

But, i want the output as shown below in picture. 

ROW_ID	DAY	STUDENT_ID	CLASS_ID	BEGIN_CNT	SUBSCRIPTION_CNT	CANCEL_CNT	ACTIVE_TODAY_CNT
1	7/1/2015	4	3	201	1	1	201
2	7/2/2015	4	3	201	2	3	200
3	7/3/2015	4	3	200	56	32	224
4	7/4/2015	4	3	224	12	54	182

here are the rules.

1. For first day  ACTIVE_CNT = (BEGIN_CNT+SUBSCR_CNT) - CANCEL_CNT

2. From second day 

                 BEGIN_CNT = Previous day ACTIVE_CNT

                 ACTIVE_CNT = (Previous day ACTIVE_CNT +SUBSCR_CNT) - CANCEL_CNT

How can i do this using t-sql in a simple way.

Thanks in advance. 

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	            

Patrick's query is showing 0 for ACTIVE_TODAY_CNT instead of the values you want.  But that is easily fixed by changing the final select so that the query becomes

;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 ROW_ID, DAY, STUDENT_ID, CLASS_ID, BEGIN_CNT, SUBSCRIPTION_CNT, CANCEL_CNT, BEGIN_CNT + SUBSCRIPTION_CNT - CANCEL_CNT AS ACTIVE_TODAY_CNT
  FROM rCTE

Tom

• Proposed as answer by Milan Das 2 hours 25 minutes ago