find total money of parent node of tree according to a particular pattern

See more:

SQL

SQL-Server

SQL-Server-2008

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.

http://demosite2.netbridgeindia.com/imagesashu/payment1.jpg[^]

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) <> 0 and c.ParentId!=@ParentId
group by c.ParentId
having COUNT(c.ParentId)>1

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

drop table #temp_table_name

Please Help