CODEWITHSUNDEEP
sql-server
Carolline
Carolline

Reputation: 75

SQL Server - How to get running cummulative difference with certain condition

I have data like this :

Ranking  | Quota | Amount
---------------------------
9503     | 2000  | 0
9408     | 1800  | 1800
9407     | 1800  | 1800
9406     | 1800  | 0
9405     | 1500  | 0
9404     | 1100  | 2000
1403     | 1000  | 0

I also have a variable contains previous amount from another query, lets say this variable name is @Previous_Amount and the value is 500. I have to calculate SUM(Amount) - @Previous_Amount then do the running difference but have to check the column Quota.

In this example, SUM(Amount) - @Previous_Amount = 5600 - 500 = 5100, so my expected result is like this :

Ranking  | Quota | Amount | Cum_Diff
------------------------------------
9503     | 2000  | 0      | 2000
9408     | 1800  | 1800   | 1800
9407     | 1800  | 1800   | 1300
9406     | 1800  | 0      | 0
9405     | 1500  | 0      | 0
9404     | 1100  | 2000   | 0
1403     | 1000  | 0      | 0

I have to split 5100 based on quota.

  1. In row 1, quota = 2000, cum_diff = 5100, so it will return max quota which is 2000.
  2. In row 2, quota = 1800, cum_diff = 5100-2000 = 3100, so it will return max quota which is 1800
  3. In row 3, quota = 1800, cum_diff = 5100-2000-1800 = 1300, so it will return 1300 because 1300 less than quota
  4. The other row will return 0

Note : the ranking column is a generated column based on certain condition, so the cum_diff will fill order by ranking desc

Anyone have idea how to do this ?

Upvotes: 0

Views: 91

Answers (2)

Shikhar Arora
Shikhar Arora

Reputation: 886

You can use the ROWS BETWEEN FOR BOTH SUM(Amount) AND SUM(QUOTA) and then a CASE expression. I've used a CTE for a more modular approach

DECLARE @Previous_Amount INT =500       

;WITH CTE AS
(
SELECT   SUM(AMOUNT) OVER (ORDER BY Ranking DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
 - @Previous_Amount - ISNULL(SUM(QUOTA) OVER (ORDER BY Ranking DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ),0)  AS ComputedDifferenc, *
FROM  @TABLE1 
)
SELECT  Ranking,Quota,Amount,CASE WHEN  ComputedDifferenc > Quota THEN Quota ELSE CASE WHEN ComputedDifferenc >0 THEN ComputedDifferenc ELSE 0 END END AS Cum_Diff

FROM CTE

Upvotes: 1

Thom A
Thom A

Reputation: 95830

You can use ROWS BETWEEN to get a cumulative SUM, and then we can use a CASE to display the appropriate values:

CREATE TABLE dbo.TestTable (Ranking int,
                            Quota int,
                            Amount int);
INSERT INTO dbo.TestTable (Ranking,
                           Quota,
                           Amount)
VALUES(9503,2000,0),
      (9408,1800,1800),
      (9407,1800,1800),
      (9406,1800,0),
      (9405,1500,0),
      (9404,1100,2000),
      (1403,1000,0);

GO

DECLARE @Previous_Amount int = 5100;

WITH CTE AS(
    SELECT TT.Ranking,
           TT.Quota,
           TT.Amount,
           @Previous_Amount - SUM(TT.Quota) OVER (ORDER BY TT.Ranking DESc
                                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative
    FROM dbo.TestTable TT)
SELECT C.Ranking,
       C.Quota,
       C.Amount,
       CASE WHEN C.Cumulative > 0 THEN C.Quota
            WHEN C.Quota + C.Cumulative > 0 THEN C.Quota + C.Cumulative
            ELSE 0
       END AS Cum_Diff
FROM CTE C
ORDER BY C.Ranking DESC;

GO
DROP TABLE dbo.TestTable;

DB<>Fiddle

(Assumes you are using a supported version of SQL Server, but I'll suggest that's a safe assumption as you haven't mentioned you are using an unsupported version.)

Upvotes: 1

Related Questions