15,612,595 members
4.00/5 (1 vote)
See more:
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
SQL
``` 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.
SQL
```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.
SQL
```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
SQL
```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 [ID] NOT IN (@SubLeftID, @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```
Posted
Updated 8-Jan-14 8:01am
v2
Philippe Mori 16-Jan-14 22:40pm
Explain what you want to do with examples.

## Solution 1

SQL
```with tree as
(
select id, parentid from Associate_Income   where parentid is null
union all
select ai.id, ai.parentid from Associate_Income ai
inner join tree t on ai.parentid = t.id
)

select distinct parentId, count(id) over (partition by parentid) from tree where parentid is not null```

This will use recursion to build a tree structure, and then uses a count to tell you how many child nodes each parent has. It will work for any number of records in the database.

Let me know if this does not solve your problem.

Oh, you want values for each date ? Then do this:

SQL
```with tree as
(
select id, parentid, cast(joingdate as date) as joiningDate from Associate_Income   where parentid is null
union all
select ai.id, ai.parentid, cast(joingdate as date) as joiningDate from Associate_Income ai
inner join tree t on ai.parentid = t.id
)

select parentId, joiningdate, count(id) from tree where parentid is not null group by parentId, joiningdate```

Honestly, the ration stuff, I cannot follow what you're asking for. Do you want to find places where 2 people joined one day, and 1 the next ?

v2
jitendra raj 9-Jan-14 6:34am
Dear Christian Graus,

I think you don't understand my question. well i illustrate my question Again in detail.

by using below my 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
http://stackoverflow.com/posts/20932342/revisions
Now I want count total number of pairs under particular node in 1:2,1:1 ratio day to day upto 5 pairs in day.

if i execute my stored procedure like as

EXEC count_pairs 'Ramesh123'

it return 3

means node 'Ramesh123' have total three pairs as shown my figure

but i want node 'Ramesh123' completed how many pairs in day ('2014-01-03 16:31:15.000')
and how many pairs in day(2014-01-04 16:31:15.000)
and how many pairs in day(2014-01-05 16:31:15.000)
.
.
.
.
up to last date as given
jitendra raj 12-Jan-14 9:42am
Dear Christian Graus,

No I want to count number of pairs day to day
Christian Graus 12-Jan-14 13:24pm
Well, it's still not clear, because my code does that. Do you mean you want to count on the same row, how many pairs there where yesterday AND today ? Do you have SQL Server 2012 ? Because it has new methods that will help with that.
jitendra raj 13-Jan-14 3:24am
but do you have any idea or code
Christian Graus 13-Jan-14 16:42pm
I have NO idea what you're asking for. That's why I keep asking. I am SURE I can solve your problem, if you'd just explain it clearly. I asked some questions, answer them. Do you have SS2012 ? Do you want to compare rows between days ? Do you want to compare them between levels ? What are you trying to do ?

## Solution 2

OK - I've read your much deeper post on Stack Overflow. Your sample data is STILL broken, and inadequate, it's only for one day. However, this should give you user ids, and the number of people who joined for that person on the date given, on the day before, and on the day before that.

with days as
(
select ai.parentid as id, count(ai.ParentID) over(partition by ai.parentid) as number, ai.joingdate from Associate_Income ai
inner join Associate_Income a2 on ai.id = a2.ParentID
)

select today.number, today.joingdate, isnull(yesterday.number, 0), isnull(dayBefore.Number, 0), today.id
from days today
left join days yesterday on today.joingdate = dateadd(dd, -1, today.joingdate)
left join days dayBefore on today.joingdate = dateadd(dd, -2, today.joingdate)

Of course, with the broken data you posted, it's impossible to test.

In future, when someone tries to help you, work with them on that site, don't tell them to read another one. I don't use Stack Overflow, I don't help there, I don't like it. If you don't want to be part of this community, why would this community help you ?

v2
jitendra raj 16-Jan-14 1:28am
I am very sorry for that and i never say that Again in future to go for another forum or check code at another forum for any one

but when we execute your code it does not return any value

please tell us how can pass parentid in above code please give some idea so that we can perform calculation for every parent node
Christian Graus 16-Jan-14 17:01pm
My code returns no values because there's no values in your sample data across three days. I will edit it so it returns 0s.
Christian Graus 16-Jan-14 17:02pm
OK - I don't care if you use more than one forum, but regard that the people in each forum hope you respect them enough to ask the full question there.

I've changed it so that it will return 0s for days there's no matches for either of the prior days. Still unsure of what you want, let me know if this helps or not.
jitendra raj 17-Jan-14 0:45am
because this code does not return any value so i can not say this code helpful for me or not if it return some value then i can checked it is helpful or not so please provide the full code which return some value again thanks for your valuable advice
Christian Graus 18-Jan-14 3:39am
My code will return values if you have a full set of data that conforms to what you seem to be asking. Your sample data can never return values from my proc. Do you know SQL at all ? Do you understand what I wrote ? I explained it - my code looks at matches for today, yesterday and the day before. I can't easily test it because your sample data was for only one day. No, I am not going to type up the SQL to insert the data below, you need to provide it in that format for me. And you need to EXPLAIN what you want. Given the sample data below, what format do you WANT the return value to be ?

Most people would be astonished that I am still trying to help you, you don't seem to want help enough to answer some basic questions.