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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ashumeerut
    New Member
    • Oct 2013
    • 3

    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:
    Code:
    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 in attachment file 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
    Code:
    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
    Attached Files
Working...