Accpac2010
Programmer
Hi all,
I am trying to create a query that will calculate a
sales commission and reset the commission to zero if the YTD commission reach certain dollar Cap.
For example I have an invoice table that has
invnumber
salesrep
invamount
Assume that salesrep get 2% of invoice AMT as a commission per month and I need to calculate his monthly commission
so it will be like:
salesrep Month commission
1 Jan 2000$
1 Feb 5000$
and so on...
Now the condition I want to include is :
if sum(ytd) > 100,000$ than commission for that month will be equal to the difference between previous month and the 100,000$ cap. In other words if we are in October and sum(YTD) commission up to August = 90,000 and sum(commission) for September = 20,000 in this case the salesrep exceeds the 100,000 cap (90,000+20,000=110,000) and I need to calculate his commission as 10,000 for september not 20,000 as he reachs the cap. Also anything after September will be zero commission even if there are sales because he reach the limit.
Any suggestion for an easy way to build this query.
Thanks
Bruce
I am trying to create a query that will calculate a
sales commission and reset the commission to zero if the YTD commission reach certain dollar Cap.
For example I have an invoice table that has
invnumber
salesrep
invamount
Assume that salesrep get 2% of invoice AMT as a commission per month and I need to calculate his monthly commission
so it will be like:
salesrep Month commission
1 Jan 2000$
1 Feb 5000$
and so on...
Now the condition I want to include is :
if sum(ytd) > 100,000$ than commission for that month will be equal to the difference between previous month and the 100,000$ cap. In other words if we are in October and sum(YTD) commission up to August = 90,000 and sum(commission) for September = 20,000 in this case the salesrep exceeds the 100,000 cap (90,000+20,000=110,000) and I need to calculate his commission as 10,000 for september not 20,000 as he reachs the cap. Also anything after September will be zero commission even if there are sales because he reach the limit.
Any suggestion for an easy way to build this query.
Thanks
Bruce