Cumulative Calculation with some methods - SQL Server

Sometime we have a need some calculation custom logic like some aggregation, pivoting and unpivoting etc. We have a same need here to do the calculation for the cumulative data from the logic. At that time we are implementing any logic which is in our mind, which are not going to find the best and easy way to do that thing and that cause the performance issue.

In a previous article I have posted for pivoting and unpivoting usage. Here I would like you to go through all the way which used to calculate cumulative data. The thing is that we can have all the ways to get it and which are the best among them, but it is totally depend our query and table data. We will look all the ways one by one and decide the best way related to logic. Before implementing it, we will first create objects required for the demo.

USE DEMO
GO

-- Creating table
IF (OBJECT_ID('CummulativeData','U') > 0)
DROP TABLE CummulativeData
GO

CREATE TABLE CummulativeData
(
TransactionId Int identity(1,1) not null,
UserAccountId int not null,
UserName varchar(500) not null,
Amount numeric(18,2),
TransactionDate datetime 
)

GO

-- Inserting sample records in table
INSERT CummulativeData
(
UserAccountId,
UserName,
Amount,
TransactionDate
)
SELECT 1234, 'ABCUSER', 250.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 350.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 150.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 100.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 300.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 650.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 50.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 100.00, GETDATE()
UNION ALL
SELECT 1234, 'ABCUSER', 450.00, GETDATE()

GO

-- Reviewing data from table
SELECT 
TransactionId,
UserAccountId,
UserName,
Amount,
TransactionDate 
FROM CummulativeData
WHERE UserAccountId = 1234
ORDER BY TransactionId

GO

After creating objects and sample records we have a turn to test drive with all the methods.

#1 way - Using temp table with while loop :

 
-- creating temp table
CREATE TABLE #tempCummulative
(
TransactionId Int ,
UserAccountId int not null,
UserName varchar(500) not null,
Amount numeric(18,2),
TransactionDate datetime ,
CummulativeAmount numeric(18,2)
)
GO

-- variables declaration
DECLARE @cnt int, @LastAmmout numeric(18,2)
SET @cnt = 1
SET @LastAmmout = 0.00

-- While loop start
WHILE((SELECT COUNT(1) FROM CummulativeData WHERE UserAccountId = 1234) >= @cnt)
BEGIN

INSERT INTO #tempCummulative
SELECT 
TransactionId,
UserAccountId,
UserName,
Amount,
TransactionDate,
@LastAmmout + Amount
FROM CummulativeData
WHERE UserAccountId = 1234
AND TransactionId = @cnt

SET @LastAmmout = (select CummulativeAmount from #tempCummulative where TransactionId = @cnt)
SET @cnt = @cnt + 1

END
-- While loop end

-- Viewing cummulative data from temp table 
SELECT 
* 
FROM #tempCummulative
GO;

#2 way - Adding column in table and while loop :

 

-- Adding column for CummulativeAmount
ALTER TABLE CummulativeData
ADD CummulativeAmount numeric(18,2)
GO

-- declaring variables
DECLARE @cnt int, @LastAmmout numeric(18,2)
SET @cnt = 1
SET @LastAmmout = 0.00

-- while loop start
WHILE((SELECT COUNT(1) FROM CummulativeData WHERE UserAccountId = 1234) >= @cnt)
BEGIN

UPDATE CummulativeData
SET @LastAmmout = @LastAmmout + ISNULL(Amount,0.00),
CummulativeAmount = @LastAmmout
WHERE UserAccountId = 1234
AND TransactionId = @cnt

SET @cnt = @cnt + 1

END
-- while loop end


-- Viewing cumulative data from table 
SELECT 
* 
FROM CummulativeData
GO

#3 way - Common Table Expression (CTE):

 
-- CTE 
;WITH cteCummulative
AS
(

SELECT TOP 1 
TransactionId,
UserAccountId,
UserName,
Amount,
TransactionDate,
Amount as CummulativeAmount  
FROM CummulativeData
WHERE UserAccountId = 1234
ORDER BY TransactionId

UNION ALL

SELECT 
c1.TransactionId,
c1.UserAccountId,
c1.UserName,
c1.Amount,
c1.TransactionDate,
CAST(c2.CummulativeAmount + c1.Amount AS numeric(18,2))
FROM CummulativeData c1
INNER JOIN cteCummulative c2
ON (c1.UserAccountId = c2.UserAccountId and c1.TransactionId = c2.TransactionId + 1 )

)

-- Viewing cummulative data from CTE 
SELECT
* 
FROM cteCummulative 

Stay tuned for more.