question

omprakashsahlive2222 avatar image
omprakashsahlive2222 asked

Display dowline member_downline

ALTER PROCEDURE [dbo].[p_member_downline] 
 
 @mem_id   varchar(20)
AS
BEGIN

declare @all_mem  table (
    [MEMBER_CODE] varchar(20),
 [RANK_POS_ID] int,
 [INTRODUCER_CODE] varchar(20),
 [INTRODUCER_RANK] int,
 [BRANCH_CODE] varchar(20),
 [INTRO_NAME] VARCHAR(50),
 [MEMBER_NAME] varchar(100),
 [lbl_id] int);
 
 

    declare @MEMBER_CODE varchar(20),
            @RANK_POS_ID int,
            @INTRODUCER_CODE varchar(20),
            @INTRODUCER_RANK  int,
            @BRANCH_CODE     varchar(20),
            @JOINING_DATE    VARCHAR(50),
            @MEMBER_NAME     varchar(100);
      
    
 declare @t table(
 [MEMBER_CODE] varchar(20),
 [RANK_POS_ID] int,
 [INTRODUCER_CODE] varchar(20),
 [INTRODUCER_RANK] int,
 [BRANCH_CODE] varchar(20),
 [JOINING_DATE] VARCHAR(50),
 [MEMBER_NAME] varchar(100));
 
 
 declare @temp table(
 [MEMBER_CODE] varchar(20),
 [RANK_POS_ID] int,
 [INTRODUCER_CODE] varchar(20),
 [INTRODUCER_RANK] int,
 [BRANCH_CODE] varchar(20),
 [JOINING_DATE] VARCHAR(50),
 [MEMBER_NAME] varchar(100));
 
 
 declare @max_lbl int;
 set @max_lbl=(select isnull(max(lbl_id),0) from @all_mem);
 
 while(@max_lbl!=16)
 begin 
 
 if(@max_lbl=0)
 begin 
 insert into @t 
 select MEMBER_CODE,RANK_POS_ID,INTRODUCER_CODE,INTRODUCER_RANK,
    BRANCH_CODE,JOINING_DATE,MEMBER_NAME 
    from MEMBERSHIP where INTRODUCER_CODE=@mem_id; 


 while((select count(*) from @t)!=0)
 begin
 declare @m_code varchar(20);
 set @m_code = (select top 1 MEMBER_CODE from @t);
 select @MEMBER_CODE=MEMBER_CODE,@RANK_POS_ID=RANK_POS_ID,
    @INTRODUCER_CODE=INTRODUCER_CODE,@INTRODUCER_RANK=INTRODUCER_RANK,
    @BRANCH_CODE=BRANCH_CODE,@JOINING_DATE=JOINING_DATE,
    @MEMBER_NAME=MEMBER_NAME 
    from @t where MEMBER_CODE=@m_code;
 insert into @all_mem values(
 @MEMBER_CODE,@RANK_POS_ID,@INTRODUCER_CODE,@INTRODUCER_RANK,
 @BRANCH_CODE,@JOINING_DATE,@MEMBER_NAME,1)
 delete from @t where MEMBER_CODE=@m_code;    
 
 end    
 
 end--end of lbl_max
 
 else 
 begin 
    
 insert into @temp 
 select MEMBER_CODE,RANK_POS_ID,INTRODUCER_CODE,INTRODUCER_RANK,
    BRANCH_CODE,MEMBER_NAME,NULL 
    from @all_mem where lbl_id=@max_lbl;
 
 
    
 while((select count(*) from @temp)!=0)
 begin
 declare @m_id varchar(20);
 set @m_id=(select top 1 MEMBER_CODE from @temp);
 
 insert into @t 
 select MEMBER_CODE,RANK_POS_ID,INTRODUCER_CODE,INTRODUCER_RANK,
    BRANCH_CODE,JOINING_DATE,MEMBER_NAME 
    from MEMBERSHIP where INTRODUCER_CODE=@m_id; 


 
 while((select count(*) from @t)!=0)
 begin

 set @m_code = (select top 1 MEMBER_CODE from @t);

 select @MEMBER_CODE=MEMBER_CODE,@RANK_POS_ID=RANK_POS_ID,
    @INTRODUCER_CODE=INTRODUCER_CODE,@INTRODUCER_RANK=INTRODUCER_RANK,
    @BRANCH_CODE=BRANCH_CODE,@JOINING_DATE=JOINING_DATE,
    @MEMBER_NAME=MEMBER_NAME 
    from @t where MEMBER_CODE=@m_code;
    
    
 insert into @all_mem values(
 @MEMBER_CODE,@RANK_POS_ID,@INTRODUCER_CODE,@INTRODUCER_RANK,
 @BRANCH_CODE,@JOINING_DATE,@MEMBER_NAME,(@max_lbl+1));
 --select * from @all_mem;
 delete from @t where MEMBER_CODE=@m_code;    
 
 end     
 
 --for temp
 delete from @temp where MEMBER_CODE=@m_id; 
 end
 
 end--end of else lbl_id max
 set @max_lbl=@max_lbl+1; 
 end --while ibi_id max 
 
 
 insert into @all_mem
 select 
 A.[MEMBER_CODE],
 A.[RANK_POS_ID],
 A.[INTRODUCER_CODE],
 A.[INTRODUCER_RANK],
 B.BRANCH_NAME AS BRANCH_CODE,
 A.MEMBER_CODE,
 A.[MEMBER_NAME],
 A.id
 
 from MEMBERSHIP  A INNER JOIN BRANCH_MASTER B ON A.BRANCH_CODE=B.BRANCH_CODE where A.MEMBER_CODE=@mem_id  
    select A.[MEMBER_CODE],
 A.[RANK_POS_ID],
 A.[INTRODUCER_CODE],
 A.[INTRODUCER_RANK],
 B.BRANCH_NAME AS BRANCH_CODE,
 A.[MEMBER_NAME]
 from @all_mem A INNER JOIN BRANCH_MASTER B ON A.BRANCH_CODE=B.BRANCH_CODE 
    
END
sql-server-2008t-sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

your question please....
1 Like 1 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.