Count Downline Members - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Count Downline Members

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2009-09-27 : 12:50:52

My Table contains following fields
CodeNo,SponsorCode
This is referal program. A guy refers to 2 guys and they 2 refers another 2-2=4.
I wan't to count complete downline of any code say 2.

CodeNo SponsorCode
1 0
2 1
3 1
4 2
5 2
6 3

by this if we count the downline of 1 then it would be 5. If we count the downline of 2 then it would be 3 & the downline of 3 would be 1 only.

I've to write STORED PROCEDURE in SQL Server. Can anyone helps. I'm new in STORED PROCEDURE.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-27 : 21:49:35

[code]
-- Sample Table & Data
DECLARE @sample TABLE
(
CodeNo int,
SponsorCode int
)
INSERT INTO @sample
SELECT 1, 0 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 3

-- Query
; WITH data
AS
(
SELECT CodeNo, SponsorCode = CodeNo
FROM @sample
WHERE CodeNo = 1

UNION ALL

SELECT s.CodeNo, d.SponsorCode
FROM @sample s
INNER JOIN data d ON s.SponsorCode = d.CodeNo
)
SELECT no_downline = COUNT(*)
FROM data
WHERE CodeNo <> SponsorCode

-- Result
/*
no_downline
-----------
5

(1 row(s) affected)
*/
[/code]

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

ravininave
Posting Yak Master

111 Posts

Posted - 2009-09-27 : 22:55:33

INSERT INTO @sample
SELECT 1, 0 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 3

I'm not getting

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-27 : 23:17:03

that is just for me to simulate your environment. I don't have your database or table or data to write the query

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

ravininave
Posting Yak Master

111 Posts

Posted - 2009-09-28 : 01:52:12

I'm quite new in SQL STORED PROCEDURES. What would be the exact query to count downline members of 1 from my database. Thanks for reply. Kindly Help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-28 : 02:11:05

just that part after the line

-- query

change @sample to your actual table name in that query

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

ravininave
Posting Yak Master

111 Posts

Posted - 2009-09-28 : 06:22:04

If I use { & } SQL STORED PROCEDURE gives error "Syntax error or sharing violation" It's find if I remove it. When I run the query I got error message "Invalid object name 'data'.". I think we are near to Solution.

ravininave
Posting Yak Master

111 Posts

Posted - 2009-09-28 : 06:27:00

Here is my Syntax which I'd written in SQL.

CREATE PROCEDURE FindDownline
(@CodeNo_1 [numeric](9))

As

Select CodeNo,SponsorCode=CodeNo from MyTableName Where CodeNo=@CodeNo_1
union all
Select s.CodeNo,d.SponsorCode FROM MyTableName s INNER JOIN data d ON s.SponsorCode=d.CodeNo

Select no_downline = count(*) from data where CodeNo<> SponsorCode
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-28 : 06:39:06

[code]
CREATE PROCEDURE FindDownline
(@CodeNo_1 [numeric](9))

As

; WITH data
AS
(
SELECT CodeNo, SponsorCode = CodeNo
FROM MyTableName
WHERE CodeNo = @CodeNo_1

UNION ALL

SELECT s.CodeNo, d.SponsorCode
FROM MyTableName s
INNER JOIN data d ON s.SponsorCode = d.CodeNo
)
SELECT no_downline = COUNT(*)
FROM data
WHERE CodeNo <> SponsorCode
[/code]

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

ravininave
Posting Yak Master

111 Posts

Posted - 2009-09-28 : 06:45:43

Thanx for immediate reply
still it doesn't works
One more thing I'm using Sql Server 2000 and Not 2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-28 : 09:44:46

well you posted in Transac-SQL (2005) forum and i posted a solution for SQL 2005.

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

khtan
In (Som, Ni, Yak)

17689 Posts