Dear friends,
I am working on a project in which members are added in a tree pattern, and get the payment accordingly.
below is my table structure, data and stored procedure
CREATE TABLE Associate_Income
(
ID varchar(30) NOT NULL,
ParentID varchar(30) NULL,
IsLeft tinyint NULL,
IsRight tinyint NULL,
joingdate datetime NOT NULL
)
go
INSERT Associate_Income
(ID, ParentID, IsLeft, IsRight, joingdate)
SELECT 'Ramesh123', NULL, NULL, NULL '2014-01-03 16:31:15.000' UNION ALL
SELECT 'Sonu', 'Ramesh123', 1, NULL, '2014-01-03 16:45:21.000' UNION ALL
SELECT 'Pawan kumar', 'Ramesh123', NULL, 1, '2014-01-04 16:50:23.000' UNION ALL
SELECT 'Ravi123', 'Sonu', 1, NULL, '2014-01-04 17:03:22.000' UNION ALL
SELECT 'Vineet123', 'Sonu', NULL, 1, '2014-01-04 17:26:01.000' UNION ALL
SELECT 'dev123', 'Ravi123', 1, NULL, '2014-01-05 19:35:16.000' UNION ALL
SELECT 'Mukesh123', 'Ravi123', NULL, 1, '2014-01-05 19:40:41.000' UNION ALL
SELECT 'poonam123', 'Vineet123', 1, NULL, '2014-01-05 19:49:49.000' UNION ALL
SELECT 'monu', 'Pawan kumar', 1, NULL, '2014-01-05 17:32:58.000' UNION ALL
SELECT 'Arti123', 'Pawan kumar', NULL, 1, '2014-01-05 19:54:35.000' UNION ALL
by using below stored procedure I can count the total number of pairs under particular node in 2:1,1:1 ratio means first pair is completed when two node added to the left side of given parent node and one node added right side of given parent node after that all pairs are completed when one node added left side and one node added right side of parent node (1:1 ratio)
example if i execute my stored procedure as follows it would return following.
EXEC count_pairs 'Ramesh123'
3
so there is 3 pairs as shown in my figure.
when we execute my stored procedure for ParentID 'sonu' it would return following.
EXEC count_pairs 'sonu'
2
so there is 2 pairs as shown in my figure.
My problem is to find the query which can return the total number of pair under particular node any given node node. day by day maximum 5 pairs in a day please any one can suggest us
CREATE proc [dbo].[count_pairs]
(
@ParentID nvarchar(50)
)
as
begin
Declare @ParentSUM SMALLINT = 0
Declare @SubLeftID nvarchar(50)
Declare @SubRightID nvarchar(50)
SELECT @SubLeftID = CASE WHEN [IsLeft] = 1 THEN [ID] ELSE @SubLeftID END
,@SubRightID = CASE WHEN [IsRight] = 1 THEN [ID] ELSE @SubRightID END
FROM Associate_Income
WHERE ParentID = @ParentID
IF @SubLeftID IS NOT NULL AND @SubRightID IS NOT NULL AND EXISTS(SELECT 1 FROM Associate_Income WHERE [IsLeft] = 1 AND ParentID = @SubLeftID)
BEGIN
SET @ParentSUM = 1
;WITH Associate_Income_CTE AS
(
SELECT [ID], [ParentID], [IsLeft], [IsRight], 0 AS [Level]
FROM Associate_Income
WHERE [ParentID] = @ParentID
UNION ALL
SELECT RecursiveMember.[ID], RecursiveMember.[ParentID], RecursiveMember.[IsLeft], RecursiveMember.[IsRight], Level + 1
FROM Associate_Income RecursiveMember
INNER JOIN Associate_Income_CTE AnchorMember
ON RecursiveMember.[ParentID] = AnchorMember.[ID]
)
SELECT @ParentSUM = @ParentSUM + COUNT([ParentID])
FROM
(
SELECT [ParentID]
,'IsLeft' AS [Direction]
,1 AS [Value]
FROM Associate_Income
WHERE [IsLeft] = 1
AND [ID] <> @ParentID
AND [ParentID] NOT IN (@ParentID, @SubLeftID)
UNION ALL
SELECT [ParentID]
,'IsRight' AS [Direction]
,1 AS [Value]
FROM Associate_Income
WHERE [IsRight] = 1
AND [ParentID] <> @ParentID
) AS Associate_Income
PIVOT
(
MAX([Value]) FOR [Direction] IN ([IsLeft], [IsRight])
) PVT
WHERE [IsLeft] IS NOT NULL AND [IsRight] IS NOT NULL
END
SELECT @ParentSUM