How to find the total number of pair under particular parent according to pattern 1:2or2:1,1:1 day to day maximum up to 5 pair caping daily

Question

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 8-Jan-14 7:51am

jitendra raj

Updated 8-Jan-14 8:01am

Matt T Heffron

v2

Add a Solution

Comments

Philippe Mori 16-Jan-14 22:40pm

Explain what you want to do with examples.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2014-01-08T10:41:00

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 ?

Posted 8-Jan-14 10:41am

Christian Graus

Updated 9-Jan-14 10:20am

v2

Comments

jitendra raj 9-Jan-14 6:34am

Dear Christian Graus,

Thanks for your reply but

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
bellow is link of MLM TREE figure please check it
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 ?

jitendra raj 14-Jan-14 12:09pm

no i don't have SS2012 i hvae ss2008 and i want to compare rows between days and level also

so how it can we perform please tells us as early as possible it's urgent for me one thing is that you have done any Multilevel Marketing Project where members are added in a tree pattern means thre is one root node and other are child node left child and right child and sub child...sub sub child ....so on please tell us

Christian Graus 14-Jan-14 16:28pm

No, I have not sold my soul to multi level marketing. That does not matter. So you want to compare rows between days. Please edit your question. Honestly, I am answering you every day and getting no close to being able to help you. Edit your question with the output format you want, explaining what the data means. Give us examples of every type of output you want, and then once we understand what you need, we can try to help. I THINK you may want a count for today and a count for yesterday. SS2012 would make this trivial, but I can do it in SS2008. I just don't want to put in a lot of time before you're clear that what I do, will answer you.

jitendra raj 15-Jan-14 3:00am

Dear Christian Graus for more details please check the following link with fig so that you can understand better my question.

http://stackoverflow.com/questions/21087884/how-to-calculate-the-total-number-of-pair-under-particular-parent-according-to-p

Christian Graus 16-Jan-14 0:04am

Why can't you ask a proper question in this forum then ? That's a lot better, but it's still not clear what you want. Try just answering my questions. Try respecting THIS forum if you want to be helped here.

Christian Graus · Answer 2 · 2014-01-15T18:14:00

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 ?

Posted 15-Jan-14 18:14pm

Christian Graus

Updated 16-Jan-14 11:01am

v2

Comments

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

thank you very much for valuable advice and reply my problem.

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.

jitendra raj 17-Jan-14 1:49am

Dear christian Graus following is the result which i want when executing my stored procedure as follows for node 'Ramesh123'

exec count_pairs 'Ramesh123'

result

Joingdate Completed Pair
2014-01-03 13:54:35.000 0
2014-01-04 14:56:35.000 0
2014-01-05 15:58:35.000 0
2014-01-06 16:54:35.000 1
2014-01-07 14:54:35.000 0
2014-01-07 15:34:35.000 1
2014-01-07 15:54:35.000 0
2014-01-09 11:54:35.000 0
2014-01-09 14:64:35.000 0
2014-01-23 17:54:35.000 1

and for 'sonu'

exec count_pairs 'sonu'

result

Joingdate Completed Pair
2014-01-03 13:54:35.000 0
2014-01-04 14:56:35.000 0
2014-01-05 15:58:35.000 0
2014-01-06 16:54:35.000 0
2014-01-07 14:54:35.000 0
2014-01-07 15:34:35.000 0
2014-01-07 15:54:35.000 1
2014-01-09 11:54:35.000 0
2014-01-09 14:64:35.000 1
2014-01-23 17:54:35.000 0