Conditional Running Total sql

Question

Hello there,

Below is my source dataset

Date Rejection Days
1/1/2018 0 10
2/1/2018 0 10
3/1/2018 0 10
4/1/2018 1 10
5/1/2018 0 10
6/1/2018 0 10
7/1/2018 2 10
8/1/2018 1 10
9/1/2018 0 10
10/1/2018 0 10
11/1/2018 0 10
12/1/2018 1 10

Running total calculation requirement :

calculate running total as rejections are 0
once we get rejection divide days by rejection + 1 and reset counter for running total and apply (divide days by rejection + 1 ) to next row
repeat both step until we reach last reporting date

Expected result with manual excel formula/calculation.

Accepted Answer

Hi @Mayur
You can create a temptable ，then use CURSOR and store each row into the temptable，please check ：

 DROP TABLE RESULTTABLE  
 CREATE TABLE RESULTTABLE  
(Datestr varchar(20),  
Rejection int,  
Days int,  
RUNNINGTOTAL int  
)  
  
  
 DECLARE @DATESTR VARCHAR(20)  
 DECLARE @REJECTION INT  
 DECLARE @DAYS INT  
  
 DECLARE @SUM INT =0  
  
  
 DECLARE db_cursor CURSOR FOR   
SELECT Datestr,Rejection, Days  
FROM TEMPTABLE   
  
OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @DATESTR,@REJECTION,  @DAYS  
  
WHILE @@FETCH_STATUS = 0    
BEGIN    
      IF @REJECTION = 0  
	  BEGIN  
         SET @SUM = @SUM+@DAYS  
	  END  
	  ELSE   
	  BEGIN  
	    SET @SUM =@DAYS/(@REJECTION+1)  
	  END  
	  INSERT INTO RESULTTABLE   
	  SELECT @DATESTR,@REJECTION,@DAYS,@SUM  
  
      FETCH NEXT FROM db_cursor INTO @DATESTR,@REJECTION,  @DAYS  
END   
  
CLOSE db_cursor    
DEALLOCATE db_cursor   
  
  
SELECT * FROM RESULTTABLE

Result：

Best Regards,
Isabella

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Conditional Running Total sql

0 additional answers

Your answer