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.
