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.
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 :
#2 way - Adding column in table and while loop :
#3 way - Common Table Expression (CTE):
Stay tuned for more.
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.
Comments
Post a Comment