February 28, 2002 at 3:19 am
Hello,
PLEASE GIVE ME A SOLUTION
IM AM USING SQLSERVER 7.0
i have a table which contains the following fields
namely refererid,code,name
this is just like a referral program
a guy can refer 3 guys under him i.e,
3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..
i NEED a query which should get the total no of downline members under a guy say 3
my table structure:
CREATE TABLE [usr] (
[referer_id] [int] NOT NULL ,
[int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
) ON [PRIMARY]
GO
some data for input:
*************************************
insert into usr values(2,'abc1')
insert into usr values(2,'abc2')
insert into usr values(2,'abc3')
insert into usr values(3,'abc4')
insert into usr values(3,'abc5')
insert into usr values(3,'abc6')
insert into usr values(6,'abc7')
insert into usr values(4,'abc8')
insert into usr values(7,'abc9')
insert into usr values(9,'abc10')
insert into usr values(9,'abc11')
insert into usr values(10,'abc12')
insert into usr values(6,'abc13')
*************************************
Example 1: total no of downline members referred by NO 2 is 13
Example 2: total no of downline members referred by NO 6 is 6 (no 6 referred
abc7,abc9,abc10,abc11,abc12,abc13)
Example 3 : total no of downline members referred by NO 4 is 1 (no 4 referred abc8)
Like this it goes...
The query or Stored procedure should seach entire records for the downline members
for a given guy.
February 28, 2002 at 5:16 am
Can you post what you have tried so far?
Andy
February 28, 2002 at 7:07 am
Dear Andy,
thank you for your response.
my stored procedure is given below:
*****************
create proc kb
@Name varchar(50)as
Set nocount on
declare @Code int
create table #Temp(Referer_ID int, Code int)
Select @Code = Code from Usr where Name = @Name
while @@Rowcount > 0
begin
insert #Temp (Referer_ID, Code)
Select Referer_id, Code from Usr
where code = @Code
Select @Code = Code from Usr where referer_id = @Code
end
Select * FROM ReferralOutput
DROP TABLE ReferralOutput
GO
*****************
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply