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.