help writing a query for multiple relationships within the same table
Posted on 2012-04-02
I have a table, built from a flat file, that contains commission relationships for agents (AGCOMCREV). I have figured out how to get the "contract type" for each agent:
SELECT AGCOMCREV.AgentCode, AGCOMCREV.[1stYrCommAgt1]*100 AS Expr1, AGCOMCREV.ContractEffectiveDate
WHERE (((AGCOMCREV.PolicyType)="0") AND ((AGCOMCREV.PolicyAgeLimit)=70));
That gets me 154 records. I used PolicyType="0" because the commission paid on that policy identifies the contract type (it is always pays the largest commission to the agent - 100 for 100%, or 90 for 90%, etc.). That query only gets me part of the way, though.
The difficult thing for me is this: there may be multiple agents earning commissions (a commission hierarchy) whenever Agt1 makes a sale. There can be up to four additional agents (Agt2, Agt3, Agt4 and Agt5). AgentCode and Agt1 are different columns but contain the same value (that's just the way the data came over). The commission fields iterate for the additional agents (1stYrCommAgt2, 1stYrCommAgt3, etc.).
How do I write the query to show the presence or lack of additional agents and the commissions each agent earns in each hierarchy? (It is always subtractive, by the way: an agent with a 105 contract over an agent with a 90 contract would be making a 15% override commission - but that is just an FYI)