January 16, 2010 at 9:58 pm
Dear Friends,
I have to calculate the business points a user obtains by referring other members. For example:
A introduces B (A gets 50 points)
A introduces C (A gets 50 points)
B introduces D (B gets 50 points)
B introduces E (B gets 50 points)
C introduces F (c gets 50 points)
C introduces G (c gets 50 points)
This chain continues ( basically it is a binary tree structure. B and C are under A's left and right respectively ). The points for A will be a sum of all the points generated by members to his left and the members to his right.
For every member in this tree the reward points should be calculated as follows:
If a member has 500 points to his left and 250 points to his right, or 500 points to his right and 250 points to his left he will get 200 reward points. After initial 250:500 or 500:250 points the member will get 100 reward points for every 100:100
Can anyone solve this query? Please, I need it for my project. Thanks a lot.
January 17, 2010 at 6:28 am
Several issues:
1) please provide sample data in a ready to use format
2) include expected result set
3) show us what you've tried so far and where you get stuck
For details reagarding the first wo points please see the first link in my signature.
Side note (since it sounds like homework...):
What would it matter to you if one of us would get an "A" for solving that puzzle?
Most of us are done with school so we have no real usage for the grade.
And you shouldn't use it either since this would be plagiarism... 😉
January 17, 2010 at 7:48 am
Sorry everyone. As I have joined this forum today only and this is my first post, I was not clear. Here is what I have done so far:
IF OBJECT_ID('TempDB..#mastertable','U') IS NOT NULL
DROP TABLE #mastertable;
CREATE TABLE #mastertable
(
ID int,
Introducer int,
MemberName nvarchar(50),
LeftMember int,
RightMember int,
BusinessValue int
)
INSERT INTO #mastertable
(ID,Introducer, MemberName,LeftMember,RightMember,BusinessValue)
SELECT 1,10, 'A', 2,3, 50 UNION ALL
SELECT 2,10, 'B', 4,5, 50 UNION ALL
SELECT 3,1, 'C', 6,7, 50 UNION ALL
SELECT 4,1, 'D', NULL,NULL, 50 UNION ALL
SELECT 5,2, 'E', NULL,NULL, 50 UNION ALL
SELECT 6,3, 'F', NULL,NULL, 50 UNION ALL
SELECT 7,4, 'G', NULL,NULL, 50
;
WITH FamilyTree
AS
(
SELECT *, BusinessValue AS Points FROM #mastertable
WHERE Introducer =1 UNION ALL
SELECT Fam.*, FamilyTree.BusinessValue As Points FROM #mastertable AS Fam
INNER JOIN FamilyTree ON Fam.Introducer=FamilyTree.ID)
SELECT SUM(Points) AS Commision FROM FamilyTree
OPTION (MAXRECURSION 0)
Sir,
The above query is calculating the total business value generated by the tree of member whose ID is '1'. Up to this mark I have succeeded. The requirement is to calculate the business generated from the left side of 1 as well as right side of 1. This will be done for every member present in the master table. Again the commission will be given after a member completes 250:500 or 500:250 points and after this for every 100:100. I hope this makes it a little more clear.
January 17, 2010 at 9:06 am
What is the business rule for the values for Introducer,LeftMember, and RightMember?
Yor sample do not show an "easy" pattern...
Example: ID=2 has Introducer=10 but is LeftMember of ID=1.
Edit: Thank your for taking the time to set up the sample data and show us what you've tried so far. Good job!
January 17, 2010 at 9:37 am
I will give you an example to make things clear. The project is a web application. A person registers with the website by purchasing a product for which he is awarded with some business points. Later, he can add other members. Now, suppose there is an existing user whose id is 10. 10 can add one member to his left and one member to his right. Although he can introduce members later below his added members. In both the cases 10 is the introducer.
Now 10 introduces 1 (1 purchases product for 50 BP)
10 introduces 2 (2 purchases product for 50 BP)
1 introduces 3(3 purchases product for 50 BP)
1 introduces 4(4 purchases product for 50 BP)
2 introduces 5(5 purchases product for 50 BP)
2 introduces 6(6 purchases product for 50 BP)
In this case if we convert it to tree 10 sits on top 1,2 on the next level and 3,4,5,6 on the last level.
So the complete tree of 10 has 300 BP in total.
I have calculated up to this mark. The output I need is
1)
for 10
Left :150 BP
Right:150 BP
2)
If Left:Right>=500:250 or
Right:Left>500:250
Commission for 10 =200
3) After
Left:Right>=500:250 or
Right:Left>500:250
for every 100:100
commission=100
and this has to be calculated for all the registered members. Thanks
January 17, 2010 at 12:20 pm
Since you already started using "left" and "right" terms in your table you might want to consider using those columns as holding the data as per the nested set model. A link to one of the numerous articles by Joe Celko: http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
January 17, 2010 at 9:04 pm
zafarthesultan (1/17/2010)
I will give you an example to make things clear. The project is a web application. A person registers with the website by purchasing a product for which he is awarded with some business points. Later, he can add other members. Now, suppose there is an existing user whose id is 10. 10 can add one member to his left and one member to his right. Although he can introduce members later below his added members. In both the cases 10 is the introducer.Now 10 introduces 1 (1 purchases product for 50 BP)
10 introduces 2 (2 purchases product for 50 BP)
1 introduces 3(3 purchases product for 50 BP)
1 introduces 4(4 purchases product for 50 BP)
2 introduces 5(5 purchases product for 50 BP)
2 introduces 6(6 purchases product for 50 BP)
In this case if we convert it to tree 10 sits on top 1,2 on the next level and 3,4,5,6 on the last level.
So the complete tree of 10 has 300 BP in total.
I have calculated up to this mark. The output I need is
1)
for 10
Left :150 BP
Right:150 BP
2)
If Left:Right>=500:250 or
Right:Left>500:250
Commission for 10 =200
3) After
Left:Right>=500:250 or
Right:Left>500:250
for every 100:100
commission=100
and this has to be calculated for all the registered members. Thanks
Heh... I thought binary tree MLM's were illegal in the US.
Anyway... there is nothing in your data that identifies if a child is to the left or to the right of a parent. Of course, it's easy to guess if you only have two children but not so easy if there are four.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2010 at 11:20 pm
Jeff Moden (1/17/2010)
zafarthesultan (1/17/2010)
I will give you an example to make things clear. The project is a web application. A person registers with the website by purchasing a product for which he is awarded with some business points. Later, he can add other members. Now, suppose there is an existing user whose id is 10. 10 can add one member to his left and one member to his right. Although he can introduce members later below his added members. In both the cases 10 is the introducer.Now 10 introduces 1 (1 purchases product for 50 BP)
10 introduces 2 (2 purchases product for 50 BP)
1 introduces 3(3 purchases product for 50 BP)
1 introduces 4(4 purchases product for 50 BP)
2 introduces 5(5 purchases product for 50 BP)
2 introduces 6(6 purchases product for 50 BP)
In this case if we convert it to tree 10 sits on top 1,2 on the next level and 3,4,5,6 on the last level.
So the complete tree of 10 has 300 BP in total.
I have calculated up to this mark. The output I need is
1)
for 10
Left :150 BP
Right:150 BP
2)
If Left:Right>=500:250 or
Right:Left>500:250
Commission for 10 =200
3) After
Left:Right>=500:250 or
Right:Left>500:250
for every 100:100
commission=100
and this has to be calculated for all the registered members. Thanks
Heh... I thought binary tree MLM's were illegal in the US.
Anyway... there is nothing in your data that identifies if a child is to the left or to the right of a parent. Of course, it's easy to guess if you only have two children but not so easy if there are four.
Nah, Binary MLM's are legal. Pyramid schemes are illegal. Pyramid schemes can be either Unilevel, matrix, or binary tree. The difference between MLM and Pyramid is that in an MLM - each distributor in the tree MUST sell product. In Pyramid schemes, you don't have to sell any product once you get up to a certain rank.
I supported multiple MLM clients and their databases.
As for this question, this is one that I would refrain from answering in this kind of forum. These kinds of problems are much more involved than providing a simple answer. You will need to build your tree and calculate BP for each distributor recursively - there are rules that need to be considered, ranks need to be considered, volume, discount pricing, regular pricing, bonus points. There is a reason this kind of work is called a commissions module - a lot goes into it.
And of course, this is the kind of work that consultants get paid good money to do.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply