--'------------------------------------------
--'          The Binary Tree Representation
--'------------------------------------------
--'                 RootNode
--'             --------|-------
--'             |              |
--'            AL1            AL2
--'       -----|-----
--'       |          |
--'      AL2        AR2
--'   ---|---
--'  |       |
--' AL3     AR3
--'------------------------------------------

Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(Null,'L','Root Node')
GO
Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(1,'L','AL1')
GO
Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(1,'R','AR1')
GO
Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(2,'L','AL2')
GO
Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(2,'R','AR2')
GO
Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(4,'L','AL3')
GO
Insert Into tbl_BinaryTree_Nodes (Nodes_IN_FK_ParentCode, Nodes_CH_xx_Position, Nodes_VC_xx_NodeData) Values(4,'R','AR3')


----------------------------------------------------------------------------
-- Get All Nodes directly under current Node
Select B.Nodes_VC_xx_NodeData, Hiera_IN_xx_NodeLevel From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ChildCode  = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ParentCode=1 

-- Get All Nodes directly under current Node in left side
Select B.Nodes_VC_xx_NodeData, Hiera_IN_xx_NodeLevel From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ChildCode  = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ParentCode=1 And H.Hiera_CH_xx_Position ='L'

-- Get All Nodes directly under current Node in right side
Select B.Nodes_VC_xx_NodeData, Hiera_IN_xx_NodeLevel From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ChildCode  = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ParentCode=1 And H.Hiera_CH_xx_Position ='R'

-- Get All Nodes directly above the current 
Select B.Nodes_VC_xx_NodeData, Hiera_IN_xx_NodeLevel From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ParentCode  = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ChildCode=7

-- Get count of Nodes at each level under a specific node
Select Hiera_IN_xx_NodeLevel, count(*) From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ChildCode  = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ParentCode=1 
Group By Hiera_IN_xx_NodeLevel

-- To find out the Nodes which got balanced due to new node entry
-- Lets us assume that the newly added node is AR3 - (which makes AL2 balanced)
-- We can use simple SQL Query or Common Table Expressions (CTE) on SQL 2005 to get the data
-- SQL Using Common Table Expressions (CTE) on SQL 2005
With CTE as (
Select B.Nodes_IN_PK_Code as PK, B.Nodes_VC_xx_NodeData as NodeData, Hiera_IN_xx_NodeLevel as NodeLevel, 
      '' as IsBalanced,
   Power(2,Hiera_IN_xx_NodeLevel)-2 as TotalNodeForBalanceTree,
   (Select Count(Hiera_IN_PK_Code) From tbl_BinaryTree_Hierarchy Where Hiera_CH_xx_Position = 'L' and Hiera_IN_FK_ParentCode=B.Nodes_IN_PK_Code) as TotalLeftChild,
   (Select Count(Hiera_IN_PK_Code) From tbl_BinaryTree_Hierarchy Where Hiera_CH_xx_Position = 'R' and Hiera_IN_FK_ParentCode=B.Nodes_IN_PK_Code) as TotalRightChild
From tbl_BinaryTree_Hierarchy H 
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ParentCode   = B.Nodes_IN_PK_Code

Where H.Hiera_IN_FK_ParentCode   is NOT NULL and H.Hiera_IN_FK_ChildCode = 7   -- (Primary key of Newly Added Node) 
)
Select CTE.PK, CTE.NodeData, CTE.NodeLevel,
Case   When ((CTE.TotalNodeForBalanceTree / 2 = TotalLeftChild) and (CTE.TotalNodeForBalanceTree / 2 = TotalRightChild)) then 'Yes' Else' No' End As IsBalanced, 
CTE.TotalNodeForBalanceTree , CTE.TotalLeftChild , CTE.TotalRightChild
From CTE