459,963 Members | 1,815 Online
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

# How to find the total money of parent node of tree and number of pair under perticula

 P: 5 Hi friend 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 as 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. ![enter image description here][1] 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 Nov 22 '13 #1