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,10,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,@iCounter 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(@iLevel) 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(@iLevel)
--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=@fMemberId)
BEGIN
SET @AllocNo=@fMemberId
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=@fMemberID
--Checking until total no of members in that level is alloted
While @iCounter<@SponserID * 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=@NextNo)
BEGIN
SET @AllocNo=@NextNo
Return
END
SET @iCounter=@iCounter+1
END
SET @iLevel = @iLevel + 1
END
END
thx for a lot. If any body wants personally help me my mail id is
po***********@yahoo.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!
