Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with query - 2

Status
Not open for further replies.

Accpac2010

Programmer
Aug 24, 2010
8
US
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
 
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
 
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:
Code:
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
 
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
 
Hi,

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

I suggest to take a look at this blog post
(first problem with pallets)

and also

- 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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top