question

mayur avatar image
0 Votes"
mayur asked mayur published

Conditional Running Total sql

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 :
1. calculate running total as rejections are 0
2. 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
3. repeat both step until we reach last reporting date

Expected result with manual excel formula/calculation.
185876-image.png


186246-image.png


sql-server-generalsql-server-transact-sqlazure-data-lake-analytics
image.png (14.9 KiB)
image.png (18.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Isabellaz-1451 avatar image
0 Votes"
Isabellaz-1451 answered mayur published

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:

185967-image.png


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.





image.png (9.9 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @IsabellaZhangMSFT-7746 , Thank you so much for the answer. Next step for this requirement is need to count difference between days until it hits rejection and then reset counter again on 0 rejection.

Also calculate Incremental Value until we reset counter.

Expected result is added to question.




0 Votes 0 ·

Hi @mayur

Can you paste your result as you did above,it be good if you can add a new question .

0 Votes 0 ·
mayur avatar image mayur Isabellaz-1451 ·

186283-image.png



Added expected result.

Date Rejection Days Running Total Formula PayBetweenRejection IncremetalValue
1/1/2018 0 10 10 G3 10 1
2/1/2018 0 10 20 G4+H3 20 2
3/1/2018 0 10 30 G5+H4 30 3
4/1/2018 1 10 5 G6/(F5+1) 35 4
5/1/2018 0 10 15 G7+(G6/(F5+1)) 15 1
6/1/2018 0 10 25 G8+H7 25 2
7/1/2018 2 10 3 G9/(F9+1) 28 3
8/1/2018 1 10 5 G10/(F10+1) 31 4
9/1/2018 0 10 15 G11+G10/(F10+1) 15 1
10/1/2018 0 10 25 G12+H11 25 2
11/1/2018 0 10 35 G13+H12 35 3
12/1/2018 1 10 5 G14/(F14+1) 40 4

0 Votes 0 ·
image.png (18.8 KiB)