MySQL Forums
Forum List  »  MySQL Query Browser

Calculations based on looping tables
Posted by: Bodi Klamph
Date: January 19, 2012 04:09PM

Hey. I have an interesting problem that I need help with. I'm hoping there is a clean SQL solution but I suspect I will have to write some code (I'm using MS Access).

I have a table of sales people's commissions. I need to know how much commission each salesperson makes. The problem is that sometimes the commission is split between multiple people.

Example table data for todays commissions:
SP1, $100
SP2, $200
SP3, $300
SP4, $400

Now for the tricky part. If SP3 makes a sale then it gets split between SP1 and SP2 using the following static table data.

Master,Base,SplitPercent
SP1,SP1,100%
SP2,SP2,100%
SP3,SP1,50%
SP3,SP2,50%
SP4,SP3,75%
SP4,SP1,25%

So if you see with SP4, it can get split multiple times, so I can't just do a simple join and then take the percent for each sales person. I somehow have to keep looping through each "Base" until the SplitPercent is 100.

I need to keep track of when the commission gets sent to the Master or the Base as a total sum.

Any ideas? Thanks.

Options: ReplyQuote


Subject
Written By
Posted
Calculations based on looping tables
January 19, 2012 04:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.