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_nam e
(
ParentId varchar(30) null,
)
;with Child as
(
select id,ParentId from tblTestingTree where id=@ParentId
union all
Select tblTestingTree. Id,tblTestingTr ee.parentId from tblTestingTree
inner join Child
on tblTestingTree. ParentId=Child. Id
)
insert into #temp_table_nam e
select c.ParentId from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.Parent Id, 0) <> 0 and c.ParentId!=@Pa rentId
group by c.ParentId
having COUNT(c.ParentI d)>1
select COUNT(*)*500 as totalmoney from #temp_table_nam e
drop table #temp_table_nam e
Please help
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_nam e
(
ParentId varchar(30) null,
)
;with Child as
(
select id,ParentId from tblTestingTree where id=@ParentId
union all
Select tblTestingTree. Id,tblTestingTr ee.parentId from tblTestingTree
inner join Child
on tblTestingTree. ParentId=Child. Id
)
insert into #temp_table_nam e
select c.ParentId from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.Parent Id, 0) <> 0 and c.ParentId!=@Pa rentId
group by c.ParentId
having COUNT(c.ParentI d)>1
select COUNT(*)*500 as totalmoney from #temp_table_nam e
drop table #temp_table_nam e
Please help