Hi,

I am trying to create a query, so as to calculate the amount for a particular id (Parent). The query should find all the children under a parent id and calculate the amount spent by all the children and the calculate the percentage that the parent should receive. I am storing all the parent-child details in one table and the purchase details in another table. Each parent can have 3 children i.e., left, middle and right. Please help me out or at least give me an idea on how i should proceed. 

Regards,

Chandra Sekhar T

• Moved by Kalman TothModerator Wednesday, December 18, 2013 5:41 AM Not db design
• Merged by Kalman TothModerator Monday, January 06, 2014 12:08 PM Same topic

Hi,

I am trying to create a query, so as to calculate the amount for a particular id (Parent). The query should find all the children under a parent id and calculate the amount spent by all the children and the calculate the percentage that the parent should receive. I am storing all the parent-child details in one table and the purchase details in another table. Each parent can have 3 children i.e., left, middle and right. Please help me out or at least give me an idea on how i should proceed. 

Regards,

Chandra Sekhar T

• Moved by Kalman TothModerator Wednesday, December 18, 2013 5:41 AM Not db design
• Merged by Kalman TothModerator Monday, January 06, 2014 12:08 PM Same topic

Is this homework?  Someone posted a similar question.

Can you post a T-SQL script so we can test as well? Thanks.

You can label each node to make the job easier:

http://www.sqlusa.com/bestpractices2005/organizationtree/

Hi,

I am trying to create the  script but dont know where to start. Let me paste the script for both the tables. 

The table where the Parent-Child id's are stored is:

CREATE TABLE [dbo].[tb_ChainSystem](
[S_No] [int] IDENTITY(1,1) NOT NULL,
[parent_ID] [nvarchar](100) NULL,
[Child_Id] [nvarchar](100) NULL,
[JoinSide] [varchar](50) NULL,
[P_ID] [int] NULL)

The table where the  purchase details are stored is:

CREATE TABLE [dbo].[tb_ProdDetails](
[Sno] [int] IDENTITY(1,1) NOT NULL,
[PName] [varchar](50) NULL,
[PCode] [varchar](50) NULL,
[Amount] [nvarchar](500) NULL,
[Name] [varchar](100) NULL,
[ID] [nvarchar](500) NULL,
[CreatedOn] [datetime] NULL
) ON [PRIMARY]

Hope this data is helpful.

Regards,

Chandra Sekhar T.

Hi Chandra.Sekhar.T,

According to your description, the relationship of the parent and children belongs to the organization tree. You can calculate the amount of parent via checking the  table of Parent-Child. There is a T-SQL statement, you can refer to it.

 

use <databasename>

go

create table parent_children

(s_no int,PID int,CID int,Joinside varchar(100),

production_ID int) 

insert into parent_children(s_no,PID,CID,Joinside,production_ID)

values(1,1,2,'yes',1),

(2,1,3,'yes',2),

(3,1,4,'Yes',3),

(4,2,5,'yes',3),

(5,2,6,'yes',4),

(6,2,7,'yes',1),

(7,3,8,'no',1),

(8,3,9,'no',2),

(9,3,10,'no',3),

(10,4,8,'yes',1),

(11,4,9,'yes',2),

(12,4,10,'yes',3)

select * from parent_children

---choose the amount of parent

with cte as

(select distinct PID,Joinside from parent_children)

select COUNT(PID) from cte

where Joinside='yes';

---choose the all children when the PID=1

select distinct (CID),* from parent_children

where PID=1 and Joinside='yes'

In addition, we need you post more information about the table of purchase details and the relationship of two table for analysis.

Thanks,
Sofiya Li

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

My Stored Procedure

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

I don't think this is homework, I am not sure how helpful it was by Kalman to merge the two threads. It appears that two different persons posted the questions. It could be though, that it is the same problem and Jitendra has taken over Chandra's task.

However, I was not able to understand the problem nor the figure. And nor the definition of pairs in this context. My assumption is that what Jitendra posted is an abstraction of the actual problem in order to not reveal intellecutal property. Possibly this makes the problem more difficult to understand for us outsiders.

I've come so far that I worked out a table definition and INSERT statements with sample data, as well as a call to the procedure which returns 3 and this appears to map to the figure, but I don't know what it means. Since I don't know what this is about, I have not made any attepmts to understand the code, but I would appreciate clarification about the underlying business rules as well as the expected results for various test cases.

CREATE TABLE Associate_Income(ID             varchar(30) NOT NULL,
                                                            ParentID varchar(30) NULL,
                                                            IsLeft     tinyint         NULL,
                                                            IsRight    tinyint         NULL,
                                                            joingdate datetime     NOT NULL)
go
INSERT Associate_Income (ID, ParentID, IsLeft, IsRight, joingdate)
     SELECT    'Ramesh123',            NULL,                    NULL,    NULL, '2014-01-03 16:31:15.000' UNION ALL
     SELECT    'Sonu',                     'Ramesh123',     1,         NULL, '2014-01-03 16:45:21.000' UNION ALL
     SELECT    'Pawan kumar',        'Ramesh123',     NULL,    1,        '2014-01-04 16:50:23.000' UNION ALL
     SELECT    'Ravi123',                'Sonu',                1,         NULL, '2014-01-04 17:03:22.000' UNION ALL
     SELECT    'Vineet123',            'Sonu',                NULL,    1,        '2014-01-04 17:26:01.000' UNION ALL
     SELECT    'dev123',                 'Ravi123',         1,         NULL, '2014-01-05 19:35:16.000' UNION ALL
     SELECT    'Mukesh123',            'Ravi123',         NULL,    1,        '2014-01-05 19:40:41.000' UNION ALL
     SELECT    'poonam123',            'Vineet123',     1,         NULL, '2014-01-05 19:49:49.000' UNION ALL
     SELECT    'monu',                     'Pawan kumar', 1,         NULL, '2014-01-05 17:32:58.000' UNION ALL
     SELECT    'Arti123',                'Pawan kumar', NULL,    1,        '2014-01-05 19:54:35.000'
go
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
END
go
EXEC count_pairs 'Ramesh123'
go
DROP PROCEDURE count_pairs
DROP TABLE Associate_Income

Dear friends,

I am working on a multilevel marketing Application (MLM  project) and  above i mention my  table data and my stored procedure by which we can count the total number of pairs under particular node  according to particular  Pattern 2:1 or 1:2,1:1 ratio  like if i execute my stored procedure

EXEC count_pairs 'Ramesh123'

it will return  3 
so there is 3 pairs as shown in my figure.
now i want to count total number of pairs day to day maximum 5 pairs in a day please any one can suggest us 
 
		

I will have to admit that I'm not particularly fond of MLM. But maybe someone else is more enthusiastic (or have no ethical considerations).

In any case, I don't understand this:

it will return  3 so there is 3 pairs as shown in my figure.now i want to count total number of pairs day to day maximum 5 pairs in a day please any one can suggest us

You want to count more, but you don't want to count more than 5?

Also, the terms 2:1, 1:2 and 1:1 are unclear to me.

Can you elaborate from the sample data you posted? Please add more sample data (as INSERT statements) if needed.

In a binary tree, you can compute the subordinates with simple math. For node (n), the left child is at (2*n) and the right child is at (2*n + 1). 

   a
  /  \
 /    \
b      c
 \    / \
  d  e  f
         /\
        g  h

CREATE TABLE BinTree
(node CHAR(10) NOT NULL, 
 location INTEGER NOT NULL PRIMARY KEY);

INSERT INTO BinTree(node, location)
VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 5), 
     ('e', 6), ('f', 7), ('g', 14), ('h', 15);

Getting the parent of a given child is trivial:

SELECT BinTree.*, @my_child
 FROM BinTree
 WHERE location
 =(SELECT FLOOR(location/2) AS parent
        FROM BinTree T1
       WHERE T1.node = @my_child); 

Finding a subtree rooted at a particular node is a little bit complicated. Note that the locations of the children of a node with location (n) are:

(2*n), (2*n) + 1
(4*n), ..., (4*n) + 3
(8*n), ..., (8*n) + 7
(16*n), ..., (16*n) + 15
 ...

The node with location (s) is a subordinate of a node with location (n) if and only if there exists (k) such that:

(2^k) * n <= s < (2^k) * (n+1)

We know that (k) exists, therefore k = FLOOR (LOG2(s/n))

In other words if:

s < (2^FLOOR(LOG2(s/n))) *(n + 1) 

then the node with location (s) is a subordinate of a node with location (n). This is easier to see with an example:

Example one:
n = 3, s = 13
13 < (2^2)* 4
13 < 16
TRUE

Example two:
n = 2, s = 12
12 < (2^2)* 3
12 < 12
FALSE

Thus we have the subordinates query:

SELECT @my_root, T1.*
  FROM BinTree AS T1, BinTree AS T2
 WHERE T2.node = @my_root
   AND T1.location
        <(FLOOR(LOG2(T1.location/T2.location))^2) *(n+1);

This predicate lets you test a location number, (j) and see if it is a descendant of the node with location number (k) at level (i).
 
 j BETWEEN((2^i) * k) AND((2^i)* k + i)

To get all of the descendants, you could use a table of sequential integers that includes integer from one to at least the depth of the tree. 

This method can be generalized for n-ary tree with a bit of algebra. If the value of (n) is known in advance, we could improve its performance by adding the node level as another column. 

Dear friends,

I provide full details here ,my database table structure ,data and stored procedure and my problem ,

please review it and provide the solution or any idea to solve my problem.

 

I am working on a project in which members are added in a tree pattern, and get the payment accordingly.

below is my table structure ,data and stored procedure

       CREATE TABLE Associate_Income
         (
          ID             varchar(30) NOT NULL,
          ParentID       varchar(30) NULL,
          IsLeft         tinyint     NULL,
          IsRight        tinyint     NULL,
          joingdate      datetime    NOT NULL
         )

go

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

My  database table Associate_Income  structure and data 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       

by using below stored procedure i can count the total number of pairs under particular node in 2:1,1:1 ratio means first pair is completed when two node added to the left side of given parent node and one node added right side of given parent node after that all pairs are completed when one node added left side and one node added right side of parent node (1:1 ratio)

example if i execute my stored procedure as follows it would return following.

EXEC count_pairs 'Ramesh123'

3

so there is 3 pairs as shown in my figure.

when we execute my stored procedure for ParentID 'sonu' it would return following.

EXEC count_pairs 'sonu'

2

so there is 2 pairs as shown in my figure.

My problem is to find the query which can return the total number of pair under particular node any given parent  node. day to day maximum 5 pairs in a day please any one can suggest us

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
 

		

Dear friends,
I am working on a Multilevel Marketing project in which members are added in a tree pattern, and get the payment accordingly.
below is my table structure, data and stored procedure

CREATETABLE Associate_Income
         (
          ID             varchar(30) NOTNULL,
          ParentID       varchar(30) NULL,
          IsLeft         tinyintNULL,
          IsRight        tinyintNULL,
          joingdate      datetimeNOTNULL
         )
       go
    

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

 

by using below stored procedure I can count the total number of pairs under particular node in 2:1,1:1 ratio means first pair is completed when two node added to the left side of given parent node and one node added right side of given parent node after that all pairs are completed when one node added left side and one node added right side of parent node (1:1 ratio)

example if i execute my stored procedure as follows it would return following.

EXEC count_pairs 'Ramesh123'
 
3

 

so there is 3 pairs as shown in my figure.

when we execute my stored procedure for ParentID 'sonu' it would return following.

EXEC count_pairs 'sonu'
 
2

it return 2

means node 'sonu' have total 2 pairs as shown my figure but i want to count total number of pairs which are completed by node  'sonu'  in day ('2014-01-03 16:31:15.000') and total number of pairs in day(2014-01-04 16:31:15.000) and total number of pairs in day(2014-01-05 16:31:15.000)

.

.

.

. up to last date as given

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

Duplicate Question: http://social.technet.microsoft.com/Forums/sqlserver/en-US/fbec7a30-cf13-48ce-aa23-b51b144c20f3/how-to-find-the-total-number-of-pair-under-particular-parent-according-to-pattern-12or2111-day?forum=transactsql

Dear friends following is the result which i want when executing my stored procedure as follows for node 'Ramesh123'

exec count_pairs 'Ramesh123'

result


   Joingdate             Completed Pair
   2014-01-03 13:54:35.000         0   
   2014-01-04 14:56:35.000         0
   2014-01-05 15:58:35.000         0
   2014-01-06 16:54:35.000         1
   2014-01-07 14:54:35.000         0
   2014-01-07 15:34:35.000         1
   2014-01-07 15:54:35.000         0
   2014-01-09 11:54:35.000         0
   2014-01-09 14:64:35.000         0
   2014-01-23 17:54:35.000         1


and for 'sonu'

  exec count_pairs 'sonu'

  result

    Joingdate                        Completed Pair
   2014-01-03 13:54:35.000         0   
   2014-01-04 14:56:35.000         0
   2014-01-05 15:58:35.000         0
   2014-01-06 16:54:35.000         0
   2014-01-07 14:54:35.000         0
   2014-01-07 15:34:35.000         0
   2014-01-07 15:54:35.000         1
   2014-01-09 11:54:35.000         0
   2014-01-09 14:64:35.000         1
   2014-01-23 17:54:35.000         0

please any one can give me some your valuable advice ,idea or solution

thanks in advance

  

I think this is something of an assignment. Lately many similar posts I have seen on this forum.

Dear Himansu ji,

it's not assignment it's problem  if you have any idea or solution please provide  me.

thanks