Help with query - 2

[Accpac2010]

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

 

[traingamer]

Sounds like the good salespeople will be quitting...

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[markros]

They still will get their maximum, just may be will try to relax after reaching it.

PluralSight Learning Library

 

[SQLBill]

Is the commission column a string (char/varchar, etc) or a number? If it is a string you will have issues comparing it and doing sums. Try this:

DECLARE @val1 VARCHAR(5)
DECLARE @val2 VARCHAR(5)
SET @val1 = '2000$'
SET @val2 = '4000$'

IF @val1 > @val2
PRINT '2000$ is greater than 4000$'

IF @val2 > @val1
PRINT '4000$ is greater than 2000$'
[\code]

Why is 2000$ greater than 4000$?  Because with strings, the comparison is done alphabetically.  Four Thousand comes before Two Thousand alphabetically.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Accpac2010]

Hey Guys -
Let's talk about the query

Here is the table:

COLUMNS ARE AS SHOWN...VALUES ARE COMMISSION DOLLARS

SLSREP YEAR JAN FEB MAR APRIL...........DEC
JOHN 2010 2000 1000 2000 5000........3000

I imagine I need to read first column and see if it exceed the 100,000 cap then add next column and do same check and so on...

If at any month I reach the cap ..the commission will be zero and only the portion that make the cap will be considered for commission.

Thanks

 

[markros]

Hi,

It's a bit complex problem with running total involved.

I suggest to take a look at this blog post

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

(first problem with pallets)

and also

http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&p=40775&hilit=running+total#p40775

- very long thread, but several interesting ideas discussed.

In worst case scenario, you may do it with the cursor.

Also, if your table is not big, we can do cross apply solution and condition on it - but I think it is not going to be a good performance.

PluralSight Learning Library