My tbltestingtree structure is ass follow:
Expand|Select|Wrap|Line Numbers
- 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
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
Expand|Select|Wrap|Line Numbers
- 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