I need the list of those left and right side members list in binary tree along with sum(Amount Invested under his left and right side).

Question

I need the list of those left and right side members list in binary tree along with sum(Amount Invested under his left and right side).

mcasuman2004

SSC Enthusiast

Points: 135
More actions
June 19, 2011 at 9:09 am

#253161

Suppose I have a tree
--' RootNode
--' --------|-------
--' | |
--' AL AR
--' -----|----- -----|-----
--' | | | |
--' ZL ZR BL BR
--' ---|-- ---|---
--' | | | |
--' CL CR DL DR
--'------------------------------------------
AL,ZL, ZR ARE LEFT SIDE NODES OF ROOTNODE
AR, BL, BR, CL, CR, DL , DR ARE THE RIGHT SIDE NODES.
In This ' AL' and 'ZL' Has Invested 5,000 EACH AND ZR INVESTED 10,000.
SO WHEN I SAY SUM(LEFTSIDE NODES) I SHOULD GET 20,000.
IN THE SAME WAY FOR RIGHT SIDE.
THIS SHOULD REPEAT FOR EACH AND EVERY NODE IN THE TREE AND SHOULD DISPLAY THE SUM\
THANKS IN ADVANCE FOR THE HELP AND PLEASE HELP ME IN THIS SCENARIO.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 1

mcasuman2004 (6/19/2011)
Suppose I have a tree
--' RootNode
--' --------|-------
--' | |
--' AL AR
--' -----|----- -----|-----
--' | | | |
--' ZL ZR BL BR
--' ---|-- ---|---
--' | | | |
--' CL CR DL DR
--'------------------------------------------
AL,ZL, ZR ARE LEFT SIDE NODES OF ROOTNODE
AR, BL, BR, CL, CR, DL , DR ARE THE RIGHT SIDE NODES.
In This ' AL' and 'ZL' Has Invested 5,000 EACH AND ZR INVESTED 10,000.
SO WHEN I SAY SUM(LEFTSIDE NODES) I SHOULD GET 20,000.
IN THE SAME WAY FOR RIGHT SIDE.
THIS SHOULD REPEAT FOR EACH AND EVERY NODE IN THE TREE AND SHOULD DISPLAY THE SUM
THANKS IN ADVANCE FOR THE HELP AND PLEASE HELP ME IN THIS SCENARIO.

Gosh... you're asking for a complete solution for a money making company (no one else would intentionally use a balanced binary tree) and you want it done for free. My recommendation is that you good folks hire a consultant to do this. I can do such a thing for a million node hierarchy including the "Downline" node count and amounts (sums) at every level for all levels in the downline of all nodes in two queries, but it's not likely that I'll give it up for free and it's also not likely that the other few folks that know how to do this will give it up for free either. I can tell you that the answer is NOT the HierarchyID in SQL Server 2008 and it's NOT Nested Sets that will do this so very easily.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chintangandhi007 Old Hand Points: 354 More actions · Answer 2

did you get the answer.

I need the same one..

I am making the study project in college.

could you please send the answer or query..

chintangandhi007 Old Hand Points: 354 More actions · Answer 3

did you get the answer.

I need the same one..

I am making the study project in college.

could you please send the answer or query..

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 4

chintangandhi007 (8/12/2016)
I am making the study project in college.

I don't mind helping someone with school but they do have to try on their own. If you're "making a study project in college", then you need to try things on your own and learn from your studies. With that thought in mind, here are two links to study. They aren't geared precisely to BINARY hierarchical structures, but they will give you some fresh ideas, especially in how to answer the usual questions asked of any node in the hierarchy.

http://www.sqlservercentral.com/articles/T-SQL/94570/

http://www.sqlservercentral.com/articles/Hierarchy/94040/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chintangandhi007 Old Hand Points: 354 More actions · Answer 5

SQL TABLE STRUCTURE : ( [MastId] [int] IDENTITY(1,1) NOT NULL, [MastCode] varchar NOT NULL, [MastName] varchar NOT NULL, [JoiningDate] [date] NOT NULL, [LU_MemberRankId] [int] NOT NULL, [TreeId_Parent] [int] NOT NULL, [TreeId_Child_Left] [int] NOT NULL, [TreeId_Child_Right] [int] NOT NULL, [TreeLevelNo] [int] NOT NULL, [SponParentId] [int] NOT NULL, [SponChildCount] [int] NOT NULL, [SponChildCount_Left] [int] NOT NULL, [SponChildCount_Right] [int] NOT NULL, [TotChildCount_Left] [int] NOT NULL, [TotChildCount_Right] [int] NOT NULL, [RemChildCount_Left] [int] NOT NULL, [RemChildCount_Right] [int] NOT NULL, [PreferenceSideId] [int] NOT NULL, [AllChildCount_Left] [int] NOT NULL, [AllChildCount_Right] [int] NOT NULL, [AllRemChildCount_Left] [int] NOT NULL, [AllRemChildCount_Right] [int] NOT NULL, [LU_MemberStatusId] [int] NOT NULL, [LU_GenderId] [int] NOT NULL, [DOB] [date] NOT NULL, [TotPurchase] [decimal](15, 2) NOT NULL, [ActivationDate] [date] NOT NULL, [RemPurchase] [decimal](15, 2) NOT NULL )

ABOVE IS MY TABLE STRUCTURE AND

I TRIED BUT NOT GETTING RESULT WHAT I WANT...

Below is my SQL Query.

WITH AllMembers AS

(

--initialization

SELECT MastId,TreeId_Child_Left,TreeId_Parent,MastCode,MastName FROM MAST_Member

WHERE MastId = 1 --@MastId

UNION ALL

--recursive execution

SELECT e.MastId,e.TreeId_Child_Left,e.TreeId_Parent, e.MastCode,e.MastName

FROM MAST_Member e

INNER JOIN AllMembers m ON e.MastId = m.TreeId_Child_Left

)

SELECT * FROM AllMembers --OPTION (MAXRECURSION 0)

GO

chintangandhi007 Old Hand Points: 354 More actions · Answer 6

and I have attached table Data which i had inserted in the datatable.

chintangandhi007 Old Hand Points: 354 More actions · Answer 7

I AM GETTING

AL,ZL ARE LEFT SIDE NODES OF ROOTNODE.

PLEASE SUGGEST WHAT THING WENT WRONG.