i am working on multi level marketing website binary tree based.Below query is returning me total number of left side and right side members. Below query is working fine. Now i want to add one more condition in the below query. That basically will be the sub query. i am not familiar with the sub queries or nested queries. so i want help from you guys.
cmd = new SqlCommand("declare @nodeid int = '"+cust_id+ "'; with cte as
(select cust_id, parentid, joinside, null left_node, null right_node
from user_detail
where cust_id = @nodeid <br>
union all <br>
select t.cust_id, t.parentid, t.joinside,ISNULL(cte.left_node, CASE WHEN t.joinside = 0 THEN 1 ELSE 0 END)
left_node,ISNULL(cte.right_node, CASE WHEN t.joinside = 1 THEN 1 ELSE 0 END)
right_node
from user_detail t inner join cte on cte.cust_id = t.parentid )
select @nodeid nodeid, SUM(left_node) LeftNodes,SUM(right_node) RightNmodes
from cte option (maxrecursion 0)", con);
What i want is: I have another table instalments in which there is a flag column which contains values "paid" and "not paid". I want to count %age of the left side and right side customers who have paid the instalments of specific id. i want this condition in the above query. i hope you guys understand my question. Thanks
Screenshot of what I want
Comments
Post a Comment