To Find Downline - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

To Find Downline

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-17 : 07:46:53

Hello Sir ,

I have to find Downline details of User such as

UserID Placement Sponsor UnderPlacement Reffered

M2 L M1
M3 R M1
M4 L M2
M5 R M2
M6 M2 L M4
M7 M1 L M6
M8 M1 L M7

it means
M1->M2
M1->M3
M2->M4
M2->M5
M2->M6
M1->M7
M1->M8

Graphical Image as:
M1
M2 M3
M4 M5
M6
M7
M8

Note: Every user can make(sponsor) infinity no of user . Bt every user having their two hands Left and Right. If user make two user it means his both hand is full. if he wants make(sponsor) more than two user then he will make user under his downline. it will be said as spilling. Users can sponsor or make infinty no of user under his downline.

some question to clearify this example as,

Q) To find Users, Sponsored by M1
Ans.
M1
~~~~

M2
M3
M4
M5
M6
M7
M8

Q) To find Users, Sponsored by M2
Ans.
M2
~~~~
M4
M5
M6
M7
M8

Q) To find Users, Sponsored by M3
Ans. NO USER AVILABLE

Q) To find Users, Sponsored by M4
Ans. SINCE M4 HAS NOT SPONSORED ANY USER, BUT USERS ARE SPONSORED BY M4'S SPONSOR(UPPER USER OF M4) AND COME(SPILL) UNDER M4. SO M4 HAVING 3 USERS AS,
M4
~~~~
M6
M7
M8

Q) To find Users, Sponsored by M5
Ans. NO USER AVILABLE

Q) To find Users, Sponsored by M6
Ans. SINCE M6 HAS NOT SPONSORED ANY USER, BUT USERS ARE SPONSORED BY M6'S SPONSOR(UPPER USER OF M6) AND COME(SPILL) UNDER M6. SO M4 HAVING 2 USERS AS,

M6
~~~~

M7
M8

Q) To find Users, Sponsored by M7
Ans. SINCE M7 HAS NOT SPONSORED ANY USER, BUT USERS ARE SPONSORED BY M7'S SPONSOR(UPPER USER OF M7) AND COME(SPILL) UNDER M7. SO M7 HAVING 1 USERS AS,

M7
~~~~
M8

Q) To find Users, Sponsored by M8
Ans. NO USER AVILABLE

===================================

now my question is, How to find users which is made by M2

please help me

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-17 : 07:59:31

we have maked a procedure, which help me to find users. but when no of user usethis process Website hangs and by this process Tempdb database size become approx 20 GB and as I terminate the process it becomes 615 KB. i am giving u that procedure , but help me as process that I use more and more time, simultaneously without affection of System.

=================

CREATE PROCEDURE [prcLeftProcess]
(
@ID varchar(20),
@FromDate DATETIME,
@EndDate DATETIME
)
AS

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM Members WHERE vaID = @ID)
BEGIN
PRINT 'Please check your ID'
RETURN -100
END

CREATE TABLE #Temp
(
vaID varchar(20) NOT NULL,
xID varchar(20) NOT NULL,
dtDOJ DATETIME NOT NULL,
Anchor INT NOT NULL
)

CREATE CLUSTERED INDEX IX_Temp ON #Temp (Anchor, xID, vaID, dtDOJ)

INSERT #Temp
(
vaID,
xID,
dtDOJ,
Anchor
)
SELECT vaID,
vspID,
dtDOJ,
CASE
WHEN 'L' IN (cPlacement, vUnderPlacement) AND vspID = @ID THEN 1
ELSE 0
END
FROM Members
WHERE vspID IS NOT NULL

UNION ALL

SELECT vaID,
vUnderSpon,
dtDOJ,
CASE
WHEN 'L' IN (cPlacement, vUnderPlacement) AND vUnderSpon = @ID THEN 1
ELSE 0
END
FROM Members
WHERE vUnderSpon IS NOT NULL

DECLARE @Anchor INT

SET @Anchor = 0

WHILE @@ROWCOUNT > 0
BEGIN
SET @Anchor = @Anchor + 1

UPDATE t
SET t.Anchor = @Anchor + 1
FROM #Temp AS t
INNER JOIN #Temp AS x ON x.vaID = t.xID
AND x.Anchor = @Anchor
WHERE t.Anchor = 0
END

SELECT vaID
FROM #Temp
WHERE dtDOJ BETWEEN @FromDate AND @EndDate
AND Anchor > 0
GROUP BY vaID
ORDER BY vaID

====

please help me to make this as Multiuser work simultaneously ,without affection of System.
please...

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-17 : 08:58:21

please help me...

Ved Prakash Jha

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 09:09:57

I am looking now. 1 Sec.

[ /fail at humor]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 09:19:09

You want to know users who have more than one set of log-on credentials (And what their username is?)?

Or

Every Time a user creates a log on you want the system to link it to the Master User?

Sorry I am not good at understanding problems. I am new to the whole forum scene.

[ /fail at humor]

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-17 : 09:28:05

As user registered on site , it is automatically shown to their Upper users. my downline is incremented.
it is online process

Ved Prakash Jha

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 09:32:49

A User registers to your site, The account is linked to the first account they created? And your 'downline' is increased +1 for every user that gets created (Who previously has atleast 1 account)?

Sorry I am trying to get it straight in my mind what you are getting at. Does anyone else understand what you are trying to do?

[ /fail at humor]

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-17 : 09:44:56

do u know MLM , it is a multi level marketing
here user mean associate.
one associate can sponsor two associate
as he sponsor or user join using their upper id or senior id
then senior can view those user in his account as user registers
there is no need to update.

Ved Prakash Jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 09:49:16

if you are using SQL 2005, use recursive CTE

KH
[spoiler]Time is always against us[/spoiler]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 10:01:52

So you want the senior staff to be able to see when junior staff register to your website?

khtan has solved this anyway :)

[ /fail at humor]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 10:04:30

vedjha, CTE - 'Content Transfer Encoding' I think (By the way)

[ /fail at humor]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 10:24:20

CTE - Common Table Expressions
http://msdn.microsoft.com/en-us/library/ms190766.aspx

KH
[spoiler]Time is always against us[/spoiler]

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-17 : 10:31:08

I have already created a procedure using CTE. as I defined upper side.
please help me ...

========================================================

CREATE PROCEDURE [prcLeftProcess]
(
@ID varchar(20),
@FromDate DATETIME,
@EndDate DATETIME
)
AS

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM Members WHERE vaID = @ID)
BEGIN
PRINT 'Please check your ID'
RETURN -100
END

CREATE TABLE #Temp
(
vaID varchar(20) NOT NULL,
xID varchar(20) NOT NULL,
dtDOJ DATETIME NOT NULL,
Anchor INT NOT NULL
)

CREATE CLUSTERED INDEX IX_Temp ON #Temp (Anchor, xID, vaID, dtDOJ)

INSERT #Temp
(
vaID,
xID,
dtDOJ,
Anchor
)
SELECT vaID,
vspID,
dtDOJ,
CASE
WHEN 'L' IN (cPlacement, vUnderPlacement) AND vspID = @ID THEN 1
ELSE 0
END
FROM Members
WHERE vspID IS NOT NULL

UNION ALL

SELECT vaID,
vUnderSpon,
dtDOJ,
CASE
WHEN 'L' IN (cPlacement, vUnderPlacement) AND vUnderSpon = @ID THEN 1
ELSE 0
END
FROM Members
WHERE vUnderSpon IS NOT NULL

DECLARE @Anchor INT

SET @Anchor = 0

WHILE @@ROWCOUNT > 0
BEGIN
SET @Anchor = @Anchor + 1

UPDATE t
SET t.Anchor = @Anchor + 1
FROM #Temp AS t
INNER JOIN #Temp AS x ON x.vaID = t.xID
AND x.Anchor = @Anchor
WHERE t.Anchor = 0
END

SELECT vaID
FROM #Temp
WHERE dtDOJ BETWEEN @FromDate AND @EndDate
AND Anchor > 0
GROUP BY vaID
ORDER BY vaID

Ved Prakash Jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 10:54:16

that is not recursive and not a CTE.

see http://msdn.microsoft.com/en-us/library/ms186243.aspx

KH
[spoiler]Time is always against us[/spoiler]

vedjha
Posting Yak Master

228 Posts

Posted - 2009-09-18 : 03:47:24

please help me...

Ved Prakash Jha

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-18 : 07:08:03