/*************************************************************************

 Handling Recursive hierarchies using the Kimball Helper Table Method
 A code sample from www.bimonkey.com
 Written by James Beresford for www.bimonkey.com on 18/09/2009
 Please contact via email at the admin account of the bimonkey.com domain

 Re-use for educational purposes is permitted but if used in
 any environment no liability is accepted unless by
 prior commercial agreement.

************************************************************************/

/*************************************************************************

Index

Step 1: Create tables
Step 2: Populate Source tables
Step 3: Populate Helper table
Step 4: Demonstrate use of Helper Table

************************************************************************/


/*************************************************************************

Step 1: Create tables

dbo.Hierarchy - contains the recursive Hierarchy
dbo.Values - contains the values for the Hierarchy
dbo.RecursiveHelper - contains the LR Values

************************************************************************/


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Hierarchy](
	[ParentLevel] [nchar](10) NOT NULL,
	[ChildLevel] [nchar](10) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Values](
	[Level] [nchar](10) NOT NULL,
	[Value] [tinyint] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[RecursiveHelper](
	[ParentKey] [nchar](10) NULL,
	[ChildKey] [nchar](10) NULL,
	[DepthFromParent] [smallint] NULL,
	[LowestFlag] [nchar](1) NULL,
	[TopmostFlag] [nchar](1) NULL
) ON [PRIMARY]

GO

/*************************************************************************

Step 2: Populate source tables

dbo.Hierarchy - contains the recursive Hierarchy
dbo.Values - contains the values for the Hierarchy

************************************************************************/

INSERT INTO [dbo].[Hierarchy]
           ([ParentLevel]
           ,[ChildLevel])
     VALUES
			('A1','A2'),
			('A1','A3'),
			('A2','A4'),
			('A2','A5'),
			('A3','A6'),
			('B1','B2'),
			('B1','B3'),
			('B2','B4'),
			('B3','B5'),
			('B3','B6'),
			('B6','B7'),
			('B6','B8')

GO

INSERT INTO [dbo].[Values]
           ([Level]
           ,[Value])
     VALUES
			('A1','43'),
			('A2','32'),
			('A3','22'),
			('A4','10'),
			('A5','33'),
			('A6','20'),
			('B1','35'),
			('B2','21'),
			('B3','23'),
			('B4','48'),
			('B5','40'),
			('B6','14'),
			('B7','17'),
			('B8','35')

GO

/*************************************************************************

Step 3: Populate Helper table

dbo.RecursiveHelper - contains the recursive Hierarchy

************************************************************************/


DECLARE @ParentNode nchar(10)
DECLARE @ChildNode nchar(10)
DECLARE @PreviousLevelNodes TABLE (Node nchar(10))
DECLARE @CurrentLevelNodes TABLE (Node nchar(10))
DECLARE @LevelDepth int

-- Cursor for each Parent Node
DECLARE Nodes CURSOR FOR
SELECT DISTINCT ParentLevel FROM [dbo].[Hierarchy]

OPEN Nodes

FETCH NEXT FROM Nodes INTO @ParentNode

WHILE @@FETCH_STATUS = 0

	-- Loop through each Parent Node
	BEGIN

		-- Create self reference entry
		INSERT INTO [dbo].[RecursiveHelper] VALUES
		(@ParentNode,@ParentNode,0,'N','N')

		-- Initialise Current Level Node Set
		INSERT INTO @CurrentLevelNodes
		SELECT ChildLevel FROM dbo.Hierarchy
		WHERE ParentLevel = @ParentNode
		
		-- Loop through each level below parent in main loop
		WHILE (SELECT COUNT(*) FROM @CurrentLevelNodes) > 0
		
			BEGIN

				-- Cursor for each child of Current parent node
				DECLARE ChildNodes CURSOR FOR

				SELECT	* 
				FROM	@CurrentLevelNodes
				
				OPEN Childnodes
				
				FETCH NEXT FROM Childnodes INTO @ChildNode
				
				WHILE @@FETCH_STATUS = 0
				
					-- Create entry in RecursiveHelper table for each child of Current Parent
					BEGIN
				
						INSERT INTO [dbo].[RecursiveHelper] VALUES
						(@ParentNode,@ChildNode,0,'N','N')
					
						FETCH NEXT FROM Childnodes INTO @ChildNode
					
					END
				
				-- Close and Deallocate Child Node cursor
				CLOSE childnodes
				DEALLOCATE childnodes
				
				-- park values
				DELETE FROM @PreviousLevelNodes
				
				INSERT INTO @PreviousLevelNodes
				SELECT * FROM @CurrentLevelNodes
				
				DELETE FROM @CurrentLevelNodes
				
				-- Get next values for currentlevelnodes
				INSERT INTO @CurrentLevelNodes
				SELECT ChildLevel
				FROM [dbo].[Hierarchy]
				WHERE ParentLevel IN (SELECT * FROM @PreviousLevelNodes)

			end		
		
		FETCH NEXT FROM Nodes INTO @ParentNode

	END 

-- Close and Deallocate Parent node cursor
CLOSE Nodes
DEALLOCATE Nodes

-- Update Topmost Flag by identifying all parent nodes with no parents
UPDATE dbo.RecursiveHelper
SET [TopmostFlag] = 'Y'
WHERE ParentKey IN (	SELECT	ParentLevel 
						FROM	dbo.Hierarchy
						WHERE	ParentLevel NOT IN	(	SELECT DISTINCT ChildLevel
														FROM	dbo.Hierarchy		)	)


-- Update Lowest Flag by identifying all child nodes with no children 
UPDATE dbo.RecursiveHelper
SET [LowestFlag] = 'Y'
WHERE ChildKey IN (	SELECT	ChildLevel 
					FROM	dbo.Hierarchy
					WHERE	ChildLevel NOT IN	(	SELECT DISTINCT ParentLevel
														FROM	dbo.Hierarchy		)	)
														
-- Create Lowest Level records
INSERT INTO dbo.RecursiveHelper
SELECT Distinct ChildKey, ChildKey,  DepthFromParent, LowestFlag, 'N' AS TopmostFlag
FROM  dbo.RecursiveHelper
WHERE LowestFlag = 'Y'



-- Update Depth From Parent indicator for all records where not yet set
DECLARE Parents CURSOR FOR
SELECT DISTINCT ParentKey
FROM dbo.RecursiveHelper
WHERE ParentKey = ChildKey and TopmostFlag = 'Y'

OPEN Parents

FETCH NEXT FROM Parents INTO @ParentNode

WHILE @@FETCH_STATUS = 0

	BEGIN
	
		SET @LevelDepth = 1
		
		UPDATE dbo.RecursiveHelper
		SET DepthFromParent = @LevelDepth
		WHERE ParentKey = @ParentNode
		AND	  ParentKey <> ChildKey
		AND		TopmostFlag = 'Y'
		
		INSERT INTO @CurrentLevelNodes 
		SELECT @ParentNode

		WHILE (SELECT COUNT(*) FROM @CurrentLevelNodes) > 0
		
			BEGIN
			
				-- Move Current Parent Nodes to Previous Parent Nodes
				DELETE FROM @PreviousLevelNodes
				
				INSERT INTO @PreviousLevelNodes
				SELECT * FROM @CurrentLevelNodes				
				
				-- Clear Parent Nodes table
				DELETE FROM @CurrentLevelNodes
			
				-- Get parents of next level down
				INSERT INTO @CurrentLevelNodes
				SELECT	DISTINCT ChildLevel
				FROM	dbo.Hierarchy
				WHERE	ParentLevel IN (	SELECT * FROM @PreviousLevelNodes	)
										
				-- Update self referencing nodes
				UPDATE dbo.RecursiveHelper
				SET DepthFromParent = @LevelDepth
				WHERE ParentKey IN (	SELECT * FROM @CurrentLevelNodes	)
				AND ParentKey = ChildKey
				
				SET @LevelDepth = @LevelDepth + 1
			
			END
				
		FETCH NEXT FROM Parents INTO @ParentNode
	
	END

CLOSE Parents
DEALLOCATE Parents

UPDATE dbo.RecursiveHelper
SET DepthFromParent = ( SELECT	DepthFromParent 
						FROM	dbo.RecursiveHelper h
						WHERE	dbo.RecursiveHelper.ChildKey = h.ChildKey
						AND		h.ParentKey = h.ChildKey	)

WHERE DepthFromParent = 0 AND TopmostFlag <> 'Y'


/*************************************************************************

Step 4: Demonstrate use of Helper Table

************************************************************************/

DECLARE @Node nchar(10)

SET @Node = 'A2'

SELECT	SUM([Value])
FROM	dbo.RecursiveHelper r
LEFT JOIN dbo.[Values] v
ON r.ChildKey = v.[Level]
where r.ParentKey = @Node