Help for best logic

[radha]

Hai all,

My problem not just releated to sql.
My problem is releated to good logic.

I should not this type of questions hear.
But just i am trying. If any body gave suggestions, or help i am the
lockest person that time.

I just started working with sql server 3 Months back.
please excuse any errors in code.
The following Code is working fine with one type of placement. but i
don't require that. I have different business logic.
Here is Actual Trinary Tree:
because of space scercity i am spliting in to rows
1 --0 Level
2 3
5 6 8 9 10 11
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

4 --1 level
12 13 14 --2 level
32 33 34 35 36 37 38 39 40 -- 3 level
and so on
1) business logic: Existing member must sponser new members (except
first member) and he can sponsor n number of members and new member has
to place under his childrens list only.

And I need this way of placement only. I don't want to store in database
who is sponsered. No need to sponser for me because if i know the
placement no of any member i can get his parents parents by a
mathematical formula.

1) if no members is there one get placed fine
2) Assume 1 sponsered 40 members
3) by execting this code i will get 2 fine
4) next time i will get 3 fine,next 4 fine,next time 5,next time
8,11,6,9,12,7,1 0,13 upto hear fine(No change)
5) next 14,17,20,23,26, 29,32,35,38,15, 18,21,24,27,30, 33,36
39,16,19,22,25, 28,31,34,37,40, 41 this placement is wrong

6) i need 14,23,32,17,26, 35,20,29,38,15, 24,33,18,27,36, 21,30,39,16,25
34,19,28,37,22, 31,40,41 so on

-------------------------
Table Name: MemCardDetails
Fields: MemberID (Numeric(32,0) Primary key
Name (Varchar(40))
-----------------------------
CREATE Procedure PBNewID
@SponserID as Numeric(36,0),
@AllocNo as Numeric(36,0) OUTPUT,

AS
BEGIN

DECLARE @fMemberId as Numeric(36,0),@ mMemberID as Numeric(36,0),@ temp1
as Numeric(36,0),@ temp2 as Numeric(36,0)
DECLARE @iLevel as SmallInt,@iCoun ter as Numeric(36,0),@ nextNo as
Numeric(36,0)
IF NOT EXISTS (SELECT MEMBERID From MemCardDetails)
BEGIN
--If there is no members Of it is the first member LEVEL is 0
--Only one member in this level
SET @AllocNo=1
Return 0
END

--Level 0 contiains 1 member
--Level 1 contains 3 members
--Level 2 contains 9 Members
--Level n contains power(3,Level)

Set @iLevel = 1
While @iLevel<=50
BEGIN

--dbo.fnMinus(@iL evel) function returns Middle number in this level
--round(total numbers in this level/2)-1
--Level 0 I won't call
--Level 1 returns 1
--Level 2 returns 4
--Level 3 returns 13 so on
SET @mMemberID = dbo.fnMinus(@iL evel)

--getting the first number in this level
--Level 1 returns 2
--level 2 returns 5
--level 3 returns 14 so on
Set @fMemberId = @SponserID * Power(3,@iLevel ) - @mMemberId
--Checking the first number in that level is empty
IF NOT EXISTS (Select MemberId from MemCardDetails where
MemberId=@fMemb erId)
BEGIN
SET @AllocNo=@fMemb erId
Return 0
END
--if first member in that level is already alloted
--assigning the variable with that
SET @temp1 = @fMemberId
SET @temp2 = @fMemberId
SET @iCounter=@fMem berID

--Checking until total no of members in that level is alloted
While @iCounter<@Spon serID * Power(3,@iLevel ) +@mMemberId
BEGIN
--incrementing 3 so next member will go under his sibling
SET @NextNo = @temp1 + 3
--Checking if our next no will exceeds max memberId in that level
If @NextNo > @SponserID *Power(3 , @iLevel) + @mMemberID
BEGIN

SET @NextNo= @temp2 + 1
SET @temp2 = @temp2 + 1
SET @temp1 = @temp2
END
Else
SET @temp1 = @NextNo
If Not Exists (Select MemberId from MemCardDetails where
MemberId=@NextN o)
BEGIN
SET @AllocNo=@NextN o
Return
END

SET @iCounter=@iCou nter+1
END
SET @iLevel = @iLevel + 1
END

END
thx for a lot. If any body wants personally help me my mail id is
po***********@y ahoo.co.in (just tell when they can available)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!