CODEWITHSUNDEEP
sql-server
Jane Doe
Jane Doe

Reputation: 79

SQL Cumulative Sum of 2 columns

INPUT:

| ID |    Class | SubDate  | RecDate   | SubAmt| RecAmt|
--------------------------------------------------------
|  1 |       123| 23/08/15 | 15/10/2015| 12710 | 10613 |
|  2 |       123| 23/09/15 | NULL      | 12710 | NULL  |
|  3 |       123| 23/10/15 | NULL      | 2096  | NULL  |
|  4 |       123| 23/11/15 | NULL      |  917  | NULL  |
|  5 |       123| 23/12/15 | NULL      |  917  | NULL  |
|  6 |       123| 23/01/16 | 03/03/2016| 83913 | 78416 |
|  7 |       123| 23/02/16 | 19/04/2016| 267688| 168507|
|  8 |       123| 23/03/16 | 24/05/2016| 217168| 10891 |
|  9 |       123| 25/04/16 | NULL      | 48661 | NULL  |
| 10 |       123| 25/05/16 | 04/07/2016| 67824 | 47921 |
| 11 |       123| 23/06/16 | 07/08/2016|111612 | 35782 |
| 12 |       123| 25/07/16 | NULL      | 63691 | NULL  |
| 13 |       123| 25/08/16 | NULL      | 30688 | NULL  |
| 14 |       123| 25/09/16 | NULL      | 30688 | NULL  |
| 15 |       224| 26/05/16 | 25/07/2016| 41325 | 33075 |
| 16 |       224| 20/06/16 | 29/08/2016| 41325 | 5875  |
| 17 |       224| 20/07/16 | NULL      | 41325 | NULL  |
| 18 |       224| 18/08/16 | NULL      | 8250  | NULL  |
| 19 |       224| 20/09/16 | NULL      | 2375  | NULL  |
--------------------------------------------------------

My condition that I have to set is:

CASE WHEN SubDate > RecDate THEN (SubAmt + RecAmt) 
ELSE SubAmt 
END AS Cumulative

Example 1: As you can see from Desired Output,

For ID 1: 23/08/15 (SubDate of ID 1) < than 15/10/2015 (RecDate of ID 1). Hence, Cumulative for ID 1 = 12710 (SubAmt of ID 1).

Example 2: For ID 3: 23/10/15 (SubDate of ID 3) > 15/10/2015 (RecDate of ID 1). Hence, Cumulative for ID 3 = 2096 (SubAmt of ID 3) + 10613 (RecAmt of ID 1) = 12710.

Example 3: For ID 8, 23/03/16 (SubDate of ID 8) > the RecDate of ID 1 & 6 15/10/2015 (RecDate of ID 1) 03/03/2016 (RecDate of ID 6)

Hence, the cumulative for ID 8: 217168 (SubAmt of ID 3) + 10613 (ID 1) + 78416 (ID 6) = 306197

Desired Output:

| ID |    Class | SubDate  | RecDate   | SubAmt| RecAmt| Cumulative
-------------------------------------------------------------------
|  1 |       123| 23/08/15 | 15/10/2015| 12710 | 10613 | 12710 
|  2 |       123| 23/09/15 | NULL      | 12710 | NULL  | 12710 
|  3 |       123| 23/10/15 | NULL      | 2096  | NULL  | 12710 
|  4 |       123| 23/11/15 | NULL      |  917  | NULL  | 11530
|  5 |       123| 23/12/15 | NULL      |  917  | NULL  | 11530
|  6 |       123| 23/01/16 | 03/03/2016| 83913 | 78416 | 94527
|  7 |       123| 23/02/16 | 19/04/2016| 267688| 168507| 278302
|  8 |       123| 23/03/16 | 24/05/2016| 217168| 10891 | 306197
|  9 |       123| 25/04/16 | NULL      | 48661 | NULL  | 306197
| 10 |       123| 25/05/16 | 04/07/2016| 67824 | 47921 | 336252
| 11 |       123| 23/06/16 | 07/08/2016|111612 | 35782 | 380040
| 12 |       123| 25/07/16 | NULL      | 63691 | NULL  | 380040
| 13 |       123| 25/08/16 | NULL      | 30688 | NULL  | 382820
| 14 |       123| 25/09/16 | NULL      | 30688 | NULL  | 382820
| 15 |       224| 26/05/16 | 25/07/2016| 41325 | 33075 | 41325
| 16 |       224| 20/06/16 | 29/08/2016| 41325 | 5875  | 41325
| 17 |       224| 20/07/16 | NULL      | 41325 | NULL  | 41325
| 18 |       224| 18/08/16 | NULL      | 8250  | NULL  | 41325
| 19 |       224| 20/09/16 | NULL      | 2375  | NULL  | 41325
--------------------------------------------------------------

Please kindly advise.

I have tried this:

SELECT [ID]
,[Class]
,[SubDate]
,[RecDate]
,[SubAmt]
,[RecAmt]
,SUM (CASE 
        WHEN SubDate  > RecDate  or RecDate   IS NULL THEN (SubAmt + RecAmt)  
        ELSE SubAmt  END ) OVER (PARTITION BY [Class] ORDER BY [ID] ROWS 
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative
FROM TableName

But it does not work and it instead gives me an output which does not fit to the requirement.

Wrong Output:

| ID |    Class | SubDate  | RecDate   | SubAmt| RecAmt| Cumulative (Wrong Output)
--------------------------------------------------------------------------
|  1 |       123| 23/08/15 | 15/10/2015| 12710 | 10613 | 12710
|  2 |       123| 23/09/15 | NULL      | 12710 | NULL  | 12710
|  3 |       123| 23/10/15 | NULL      | 2096  | NULL  | 12710
|  4 |       123| 23/11/15 | NULL      |  917  | NULL  | 12710
|  5 |       123| 23/12/15 | NULL      |  917  | NULL  | 12710
|  6 |       123| 23/01/16 | 03/03/2016| 83913 | 78416 | 96623
|  7 |       123| 23/02/16 | 19/04/2016| 267688| 168507| 364312
|  8 |       123| 23/03/16 | 24/05/2016| 217168| 10891 | 581480
|  9 |       123| 25/04/16 | NULL      | 48661 | NULL  | 581480
| 10 |       123| 25/05/16 | 04/07/2016| 67824 | 47921 | 649304
| 11 |       123| 23/06/16 | 07/08/2016|111612 | 35782 | 760917
| 12 |       123| 25/07/16 | NULL      | 63691 | NULL  | 760917
| 13 |       123| 25/08/16 | NULL      | 30688 | NULL  | 760917
| 14 |       123| 25/09/16 | NULL      | 30688 | NULL  | 760917
| 15 |       224| 26/05/16 | 25/07/2016| 41325 | 33075 | 41325
| 16 |       224| 20/06/16 | 29/08/2016| 41325 | 5875  | 82650
| 17 |       224| 20/07/16 | NULL      | 41325 | NULL  | 82650
| 18 |       224| 18/08/16 | NULL      | 8250  | NULL  | 82650
| 19 |       224| 20/09/16 | NULL      | 2375  | NULL  | 82650
--------------------------------------------------------------

Please help me to kindly advise how I should change the SQL code as well as how i should fit the case statement. Thanks everyone!

Upvotes: 2

Views: 525

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

When RecDate is NULL, then SubDate > RecDate condition will fail since NULL is unknown and cannot be compared against any value.

Try something like this

SUM(CASE WHEN SubDate > RecDate or RecDate IS NULL THEN (SubAmt + RecAmt) 
ELSE SubAmt 
END AS Cumulative) OVER(PARTITION BY [Class] ORDER BY [ID] ROWS BETWEEN 
UNBOUNDED PRECEDING AND CURRENT ROW)

Upvotes: 1

Related Questions