Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Calculating this sort of thing by counting nodes in a treeview is pretty silly, you will be doing the calculation after binding it to a UI element. </p> <p>In this particular case, i'm also not a fan of doing it using LINQ, while LINQ will do it very well, it has the following drawbacks:</p> <ul> <li>you should let the database server do the hard work <b>where possible</b>, it is what it is for</li> <li>if levels are added or altered at some stage in the future, it means you have several more locations that need refactoring</li> <li>it can be easier to roll out a change to a database stored proc than a change to compiled code</li> </ul> <p>You haven't specified what database you are using, i am going to assume SQL. You should easily be able to calculate this if you have the names of the people and a key to who referred them in a self referencing table in the database.</p> <p>Your table will look like this:</p> <pre><code>int RecordID varchar(100) Name int ReferrerID </code></pre> <p>Then for the data it will look like this (using your examples from above):</p> <pre><code>_______________________________________________ | RecordID | Name | ReferrerID | _______________________________________________ | 1 | Person 1 | null | | 2 | Person 2 | 1 | | 3 | Person 3 | 1 | | 4 | Person 4 | 2 | | 5 | Person 5 | 2 | | 6 | Person 6 | 3 | | 7 | Person 7 | 3 | _______________________________________________ </code></pre> <p>I am also going to use a table called <code>Levels</code> containing the commission amount for each level. In this table i am assigning 0.50 for each Level 1 referral, and 0.25 for each Level 2 referral:</p> <pre><code>int LevelID money LevelAmount </code></pre> <p>Now using your self referencing table, you can calculate what you need for level 1 and level 2 by joining it on itself twice. This sql will give you the initial table:</p> <pre><code>SELECT p.RecordID ,p.PersonName ,Level1Referral.PersonName ,Level1Commission.LevelAmount ,Level2Referral.PersonName ,Level2Commission.LevelAmount FROM People p LEFT JOIN People Level1Referral ON Level1Referral.ReferrerID = p.RecordID LEFT JOIN Levels Level1Commission ON Level1Referral.ReferrerID IS NOT NULL AND Level1Commission.LevelID = 1 LEFT JOIN People Level2Referral ON Level2Referral.ReferrerID = Level1Referral.RecordID LEFT JOIN Levels Level2Commission ON Level2Referral.ReferrerID IS NOT NULL AND Level2Commission.LevelID = 2 </code></pre> <p>All i do is join the table on itself for each possible commission level, if you were to introduce a commission for 3rd level referrals then you would just add another join. </p> <p>If you run this statement, you will notice though how the <code>Level1Referral.PersonName</code> has a duplicated entry for each Level 2 referral, this is not optimal and is not easy to factor out with grouping. What we can do however is have a surrounding SELECT statement and apply some grouping to that, and in the process calculate the amount of commission for each level. Here is the final SQL statement that does what you need:</p> <pre><code>SELECT CommissionEarnerID ,CommissionEarnerName ,COUNT(DISTINCT L1Referral) AS [Number Of L1 Referrals] ,COUNT(DISTINCT L1Referral) * (SELECT LevelAmount FROM Levels WHERE LevelID = 1) AS [Level 1 Commission] ,COUNT(DISTINCT L2Referral) AS [Number Of L2 Referrals] ,COUNT(DISTINCT L2Referral) * (SELECT LevelAmount FROM Levels WHERE LevelID = 2) AS [Level 2 Commission] FROM ( SELECT p.RecordID AS CommissionEarnerID ,p.PersonName AS CommissionEarnerName ,Level1Referral.RecordID AS L1Referral ,Level1Referral.PersonName AS L1ReferralName ,Level2Referral.RecordID AS L2Referral ,Level2Referral.PersonName AS L2ReferralName FROM People p LEFT JOIN People Level1Referral ON Level1Referral.ReferrerID = p.RecordID LEFT JOIN People Level2Referral ON Level2Referral.ReferrerID = Level1Referral.RecordID ) x GROUP BY CommissionEarnerID ,CommissionEarnerName </code></pre> <p>I tidied the statement up a little, and put in a few column names. This produces a resultset that looks like this:</p> <pre><code>_____________________________________________________________________________ | Commission | Commission | Number | Level 1 | Number | Level 2 | | Earner ID | Earner | of L1 | Commission | of L2 | Commission | | | Name | Referrals | | Referrals | | _____________________________________________________________________________ | 1 | Person 1 | 2 | 1.00 | 4 | 1.00 | | 2 | Person 2 | 2 | 1.00 | 0 | 0.00 | | 3 | Person 3 | 2 | 1.00 | 0 | 0.00 | | 4 | Person 4 | 0 | 0.00 | 0 | 0.00 | | 5 | Person 5 | 0 | 0.00 | 0 | 0.00 | | 6 | Person 6 | 0 | 0.00 | 0 | 0.00 | | 7 | Person 7 | 0 | 0.00 | 0 | 0.00 | _____________________________________________________________________________ </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to calculate commision based on referred memebrs?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USslugster
    1. USslugster
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to calculate commision based on referred memebrs?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload