find total money of parent node of tree according to a particular pattern actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly.

My tbltestingtree structure is ass follow:

        Id         ParentId        IsLeft           IsRight
         1          Null             Null             Null
         2           1                1               Null
         3           1               Null               1
         4           2                1               Null
         5           2               Null               1
         6           3                1               Null
         7           3               Null               1
         8           4                1               Null
         9           4               Null               1
         10          5                1               Null


the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.

My problem is to find the query which can return the total income of any given  node.



According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.

According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has  no pairs 

According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)

one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)

I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$,  but the query has not been able to consider the first condition that is the 2:1 condition 

        declare @ParentId as int
         set @ParentId=1

       create table #temp_table_name
        (
          ParentId varchar(30) null,
         )


      ;with Child as
       (
    select id,ParentId from tblTestingTree where id=@ParentId
    union all
    Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree 
    inner join Child 
    on tblTestingTree.ParentId=Child.Id
       )

  insert into #temp_table_name
  select c.ParentId  from tblTestingTree T join Child c
  on c.Id=t.Id
  WHERE ISNULL(T.ParentId, 0) &lt;&gt; 0 and c.ParentId!=@ParentId
  group by c.ParentId
  having COUNT(c.ParentId)&gt;1 

  select COUNT(*)*500 as totalmoney from #temp_table_name

  drop table #temp_table_name


Please help
Test Data:

memberid    MemberID    joiningposition packagetype
   RPM00000     NULL         Root   free
   RPM71572     RPM00000    Left    Royal
   RPM323768    RPM00000    Right   Royal
   RPM715790    RPM71572    Left    free
   RPM323769    RPM71572    Right   free
   RPM715987    RPM323768   Left    free
   RPM323985    RPM323768   Right   free
   RPM733333    RPM323985   Right   free
   RPM324444    RPM715987   Right   Royal


create a table to store final value

CREATE TABLE [dbo].[Wallatpayout]
(   
    [childid] [varchar](50) NULL,
    [joiningposition] [varchar](50) NULL,
    [DateofJoing] [varchar](50)    NULL,
    [packagetype] [varchar](50) NULL,
    [Total] [int] NULL,
    [FirstPayoutstatus] [varchar](30) NULL,
    [DateofPayout] [datetime]    NULL 
)

create procedure [dbo].[sunnypro] as 
DECLARE @pId varchar(40) = 'RPM00000'; 
Declare @Id int set @Id=(select id from registration where hildid=@pId)      
begin

-- Recursive 
CTE WITH R AS (
    -- Anchor
    SELECT 
        BU.DateofJoing,
        BU.childid,
        BU.joiningposition,
        BU.packagetype
        FROM registration AS BU
        WHERE
        BU.MemberID = @pId and
       BU.joiningposition IN ('Left', 'Right')
        or BU.packagetype in('Royal','Platinum','Majestic')
         and BU.Id&gt;@id
   UNION All
    -- Recursive part
    SELECT      
         BU.DateofJoing,
         BU.childid,
         R.joiningposition,
        BU.packagetype   
    FROM R
    JOIN registration AS BU
        ON BU.MemberID = R.childid
    WHERE
        BU.joiningposition IN ('Left', 'Right') and
      BU.packagetype in('Royal','Platinum','Majestic')
     and BU.Id&gt;@id ) 
INSERT INTO Wallatpayout
           (childid
           ,packagetype
           ,joiningposition
           ,DateofJoing
           ,Total)
-- Final groups of nodes found 
SELECT top 3
R.childid,    R.packagetype,
    R.joiningposition,
    R.DateofJoing,
    Total = COUNT_BIG(*) FROM R where R.packagetype in('Royal','Platinum','Majestic') 
GROUP BY
R.childid,
    R.joiningposition,
    R.DateofJoing,
    R.packagetype
    OPTION (MAXRECURSION 0); 
end


This code is helpful for multi-level marketing, to find all left node and right node by passing particular id or parentid.