How to find the total number of pair under particular parent according to pattern 1:2

[jitenrajput49]

My tbltestingtree structure is as follow:

     ID            ParentID       IsLeft  IsRight             joingdate
     Ramesh123       NULL           NULL       NULL              2014-01-03 16:31:15.000
     Sonu            Ramesh123      1          NULL              2014-01-03 16:45:21.000
     Pawan kumar     Ramesh123      NULL       1                 2014-01-04 16:50:23.000
     Ravi123         Sonu           1          NULL              2014-01-04 17:03:22.000
     Vineet123       Sonu           NULL       1                 2014-01-04 17:26:01.000
     dev123          Ravi123        1          NULL              2014-01-05 19:35:16.000
     Mukesh123       Ravi123        NULL       1                 2014-01-05 19:40:41.000
     poonam123       Vineet123      1          NULL              2014-01-05 19:49:49.000 
     monu            Pawan kumar    1          NULL              2014-01-05 17:32:58.000
     Arti123         Pawan kumar    NULL       1                 2014-01-05 19:54:35.000        
 
 

My problem is to find the query which can return the total number of pair under particular node any given node. day by day

CREATE proc [dbo].[count_pairs]
(
@ParentID nvarchar(50)
 
)
as
begin
Declare @ParentSUM SMALLINT = 0
Declare @SubLeftID nvarchar(50)
Declare @SubRightID nvarchar(50)
SELECT @SubLeftID = CASE WHEN [IsLeft] = 1 THEN [ID] ELSE @SubLeftID END
      ,@SubRightID = CASE WHEN [IsRight] = 1 THEN [ID] ELSE @SubRightID END
FROM  Associate_Income
WHERE ParentID = @ParentID
 
 
IF @SubLeftID IS NOT NULL AND @SubRightID IS NOT NULL AND EXISTS(SELECT 1 FROM  Associate_Income WHERE [IsLeft] = 1 AND ParentID = @SubLeftID)
BEGIN
 
    SET @ParentSUM = 1
 
    ;WITH  Associate_Income_CTE AS
    (
 
        SELECT [ID], [ParentID], [IsLeft], [IsRight], 0 AS [Level] 
        FROM Associate_Income
        WHERE [ParentID] = @ParentID
        UNION ALL
        SELECT RecursiveMember.[ID], RecursiveMember.[ParentID], RecursiveMember.[IsLeft], RecursiveMember.[IsRight], Level + 1 
        FROM Associate_Income RecursiveMember
        INNER JOIN  Associate_Income_CTE AnchorMember
            ON RecursiveMember.[ParentID] = AnchorMember.[ID]
    )
    SELECT @ParentSUM = @ParentSUM + COUNT([ParentID]) 
    FROM
    (
        SELECT [ParentID]
              ,'IsLeft' AS [Direction]
              ,1 AS [Value]
        FROM  Associate_Income
        WHERE [IsLeft] = 1
            AND [ID] <> @ParentID --AND [ID] NOT IN (@SubLeftID, @ParentID)
            AND [ParentID] NOT IN (@ParentID, @SubLeftID)
        UNION ALL
        SELECT [ParentID]
              ,'IsRight' AS [Direction]
              ,1 AS [Value]
        FROM  Associate_Income
        WHERE [IsRight] = 1
            AND [ParentID] <> @ParentID
    ) AS  Associate_Income
    PIVOT
    (
        MAX([Value]) FOR [Direction] IN ([IsLeft], [IsRight])
    ) PVT
    WHERE [IsLeft] IS NOT NULL AND [IsRight] IS NOT NULL
 
END
 
SELECT @ParentSUM
 
 

above is my stored procedure by which we can count total number of pairs under particular parent according to a particular pattern 1:2or2:1,1:1

please any one suggest us how can count day to day total number of pairs under particular node maximum up to 5 pair capping daily

[Rabbit]

Your question is difficult to understand because you leave so many of your terms undefined.

What I can say is that your table structure is not good for storing hierarchical data. Instead, you should use a modified preorder tree traversal algorithm. You can read the details of said structure here: http://www.sitepoint.com/hierarchical-data-database-2/