Monday, June 29, 2015

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.

Monday, June 22, 2015

PERSISTED Columns with HierarchyId Datatype - SQL Server 2008

I think you have already read all the articles related to HierarchyId in the past And hope you understood the concept and usage of it. You also experienced with performance by using HierarchyId datatype as I have posted the performance review in earlier posts as well.

With earlier posts you got some basic methods or functions as how we get the hierarchy data levels, root node, string path. Also, some other methods like as how can we get up-line & down-line modes. Here I am going to present the same, but as Persisted column. So we do not need to write those functions in query level every time. Let us see the workaround for that.

How can ?
We need to create one table and define those columns as a function call as a PERSISTED. We will look the methods to get the hierarchical data without defining columns as Persisted columns and we will use those function calls at the query level. The scripts for the same are as follows.
-- Creating objects
IF (OBJECT_ID('TblHierarchyStructure','U') > 0)
DROP TABLE TblHierarchyStructure
GO

CREATE TABLE TblHierarchyStructure
  (
     ItemId        INT,
     ParentItemId  INT,
     ItemOrder     INT,
     ItemName      VARCHAR(100),
     HierarchyNode HIERARCHYID
  )

GO

-- Inseting records in tables for the demo
INSERT INTO TblHierarchyStructure
            (ItemId,
             ParentItemId,
             ItemOrder,
             ItemName,
             HierarchyNode)
SELECT 1,
       NULL,
       1,
       'RootItem',
       HierarchyId::Parse('/')
UNION ALL
SELECT 2,
       1,
       1,
       'FirstItem',
       HierarchyId::Parse('/1/')
UNION ALL
SELECT 3,
       1,
       2,
       'SecondItem',
       HierarchyId::Parse('/2/')
UNION ALL
SELECT 4,
       1,
       3,
       'ThirdItem',
       HierarchyId::Parse('/3/')
UNION ALL
SELECT 5,
       2,
       1,
       'FourthItem',
       HierarchyId::Parse('/1/1/')
UNION ALL
SELECT 6,
       4,
       1,
       'FifthItem',
       HierarchyId::Parse('/3/1/')
UNION ALL
SELECT 7,
       5,
       1,
       'SixthItem',
       HierarchyId::Parse('/1/1/1/')
UNION ALL
SELECT 8,
       5,
       2,
       'SeventhItem',
       HierarchyId::Parse('/1/1/2/')
UNION ALL
SELECT 9,
       5,
       3,
       'NinthItem',
       HierarchyId::Parse('/1/1/3/')
UNION ALL
SELECT 10,
       8,
       1,
       'TenthItem',
       HierarchyId::Parse('/1/1/2/1/')

GO

-- Usinf HierarchyId functions at query level and see output.
SELECT *,
       HierarchyNode.ToString()                AS ItemNodeString,
       HierarchyNode.GetLevel()                AS ItemNodeLevel,
       HierarchyNode.GetAncestor(1)            AS ParentNode,
       HierarchyNode.GetAncestor(1).ToString() AS ParentNodeString
FROM   TblHierarchyStructure
GO

Now we will look the methods to get the hierarchical data with defining columns as Persisted columns and we will use those functions call at the column level. The scripts for the same are as follows.
-- Creating objects
IF (OBJECT_ID('TblHierarchyStructure','U') > 0)
DROP TABLE TblHierarchyStructure
GO

CREATE TABLE TblHierarchyStructure
  (
     ItemId        INT,
     ParentItemId  INT,
     ItemOrder     INT,
     ItemName      VARCHAR(100),
     HierarchyNode HIERARCHYID NOT NULL PRIMARY KEY,
     ItemNodeString AS HierarchyNode.ToString() PERSISTED,
     ItemNodeLevel AS HierarchyNode.GetLevel() PERSISTED,
     ParentNode AS HierarchyNode.GetAncestor(1) PERSISTED,
     ParentNodeString AS HierarchyNode.GetAncestor(1).ToString()
  )

GO

-- Inserting sample records here 
INSERT INTO TblHierarchyStructure
            (ItemId,
             ParentItemId,
             ItemOrder,
             ItemName,
             HierarchyNode)
SELECT 1,
       NULL,
       1,
       'RootItem',
       HierarchyId::Parse('/')
UNION ALL
SELECT 2,
       1,
       1,
       'FirstItem',
       HierarchyId::Parse('/1/')
UNION ALL
SELECT 3,
       1,
       2,
       'SecondItem',
       HierarchyId::Parse('/2/')
UNION ALL
SELECT 4,
       1,
       3,
       'ThirdItem',
       HierarchyId::Parse('/3/')
UNION ALL
SELECT 5,
       2,
       1,
       'FourthItem',
       HierarchyId::Parse('/1/1/')
UNION ALL
SELECT 6,
       4,
       1,
       'FifthItem',
       HierarchyId::Parse('/3/1/')
UNION ALL
SELECT 7,
       5,
       1,
       'SixthItem',
       HierarchyId::Parse('/1/1/1/')
UNION ALL
SELECT 8,
       5,
       2,
       'SeventhItem',
       HierarchyId::Parse('/1/1/2/')
UNION ALL
SELECT 9,
       5,
       3,
       'NinthItem',
       HierarchyId::Parse('/1/1/3/')
UNION ALL
SELECT 10,
       8,
       1,
       'TenthItem',
       HierarchyId::Parse('/1/1/2/1/')

GO

-- We have not using HierarchyId functions at query level
-- and using them at columns level as Persisted
SELECT 
*
FROM   TblHierarchyStructure 

GO

I hope you liked this post about Persisted columns with HierarchyID new datatype. Share your experience if you know this type of the usage.
X