473,394 Members | 1,971 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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:
Expand|Select|Wrap|Line Numbers
  1. Id         ParentId        IsLeft           IsRight
  2.              1          Null             Null             Null
  3.              2           1                1               Null
  4.              3           1               Null               1
  5.              4           2                1               Null
  6.              5           2               Null               1
  7.              6           3                1               Null
  8.              7           3               Null               1
  9.              8           4                1               Null
  10.              9           4               Null               1
  11.              10          5                1               Null
  12.  
**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
Expand|Select|Wrap|Line Numbers
  1. declare @ParentId as int
  2.              set @ParentId=1
  3.  
  4.            create table #temp_table_name
  5.             (
  6.               ParentId varchar(30) null,
  7.              )
  8.  
  9.  
  10.           ;with Child as
  11.            (
  12.         select id,ParentId from tblTestingTree where id=@ParentId
  13.         union all
  14.         Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree 
  15.         inner join Child 
  16.         on tblTestingTree.ParentId=Child.Id
  17.            )
  18.  
  19.       insert into #temp_table_name
  20.       select c.ParentId  from tblTestingTree T join Child c
  21.       on c.Id=t.Id
  22.       WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
  23.       group by c.ParentId
  24.       having COUNT(c.ParentId)>1 
  25.  
  26.       select COUNT(*)*500 as totalmoney from #temp_table_name
  27.  
  28.       drop table #temp_table_name
  29.  
Please Help
Attached Images
File Type: jpg payment1.jpg (39.0 KB, 55 views)
Oct 5 '13 #1
0 1042

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Jeff | last post by:
I'd like to write an XPath expression that gets a node with 2 particular children. I thought this would work: /AAA/BBB/CCC/following-silbing::DDD/parent::node() on xml like this: AAA BBB
12
by: Dino L. | last post by:
I am putting data from DataTable to treeView foreach( DataRow aRow in aTable.Rows) { TreeNode tnode = new TreeNode(aRow.ToString() + aRow.ToString() + " " + aRow.ToString());...
3
by: MrNobody | last post by:
Ok, so I'm absoultely positive this was working last night, but today I'm getting this error in my program when I try to add a child node to a parent node of a tree... The error says: ...
5
by: rmlakshmanan | last post by:
In javascript a tree structure node i have a subnode and it has parentnode and that parentnode has one parent how do i get all parent node when i click the subnode
14
by: neerajb | last post by:
Hi, I am having an XML document(input.xml) which is showing the menu heirarchy used in my application.My requirement is to add "submenu" tag to those menuitems who are having the child menuitems...
4
by: MA | last post by:
Hi, How to access the total number of child nodes from a parent node. For example, I would like to get the total number of child nodes from <parent1and <parent2node. The SelectNodes method...
9
by: raylopez99 | last post by:
What's the best way of implementing a multi-node tree in C++? What I'm trying to do is traverse a tree of possible chess moves given an intial position (at the root of the tree). Since every...
2
by: Rob Stevens | last post by:
When restoring nodes to a tree, how can you tell what is the parent of that node? Also how can you get a handle to the parent node, this way you can insert the node into the correct place? ...
2
by: slizorn | last post by:
hi guys, i need to make a tree traversal algorithm that would help me search the tree.. creating a method to search a tree to find the position of node and to return its pointer value basically i...
1
by: SWETA123 | last post by:
I will very helpful to you if you can help me out in my below problem. I am not getting idea what should be the right approach for this. Here is my problem. I have below (sample)xml. <Rules>...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.