June 19, 2011 at 9:09 am
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.
June 19, 2011 at 7:13 pm
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
Change is inevitable... Change for the better is not.
August 12, 2016 at 4:56 am
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..
August 12, 2016 at 4:57 am
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..
August 14, 2016 at 10:57 am
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
Change is inevitable... Change for the better is not.
August 17, 2016 at 1:49 am
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
August 17, 2016 at 1:56 am
and I have attached table Data which i had inserted in the datatable.
August 17, 2016 at 1:57 am
I AM GETTING
AL,ZL ARE LEFT SIDE NODES OF ROOTNODE.
PLEASE SUGGEST WHAT THING WENT WRONG.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply