SQL to Group based on Commission Rate

[markphsd]

I would like to write a sql statement that groups sales based on a range of values of a separate commission tier schedule.

So basically there would be two tables, the sales table and the commission rate table.

for example the sales person has three tier rates:
5% commission at sales 10,000-20,000
10% commission at sales 20,000-30,000
30% commssion at sales 30,000-1,000,000

the Statement would output
Each row would be SalesTotal,Rate,CommissionTotal

It seems like SQL should be able to do this, i just don't know how... any ideas?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[starsky51]

This would give the desired result, assuming every employee used the same commission scale. You would have to alter it slightly to accomodate multiple employee types with different commission scales.

SELECT s.SalesTotal, c.Rate, (s.SalesTotal * c.Rate) AS CommissionTotal
FROM Sales s LEFT JOIN Commission c ON s.SalesTotal BETWEEN c.SalesMin AND c.SalesMax;

 

[markphsd]

Multiple scales was the part I was trying to get to.

It would basically be a GROUP BY that has a where clause as the field. GROUP BY Range

So for every given range there's a row with a total.

Your statement would require me to do a statement for each commission scale. which I was trying to avoid.



Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[starsky51]

Just want to make sure I've got this right. The resulting data set that you want to end up with will look a bit like this?:

StaffType | Range          | Commission | Commission Total
----------------------------------------------------------
Sales     | 10000-19999    | 5%         | 3255
Sales     | 20000-29999    | 10%        | 4560
Sales     | 30000-1000000  | 30%        | 13050
Manager   | 20000-49999    | 20%        | 6030
Manager   | 50000-1000000  | 30%        | 12000

 

[markphsd]

Awesome, I was clear. Yes that's exactly what I"m now doing in vb rather than sql.


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.