Tiered commissions in SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tiered commissions in SQL?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-09 : 07:34:41

Tracy writes "In a company there are 2 groups of people. One group gets paid on commission 50% for all gross revenue they bring in. Another group gets paid on commission, but on a tiered basis. That is, someone can make:

50% on 0 to 100,000
55% on 100,001 to 250,000
60% on 250,001 and above

Someone in the tiered group has been working since Jan 1 and has passed the 150,000 mark on March 15th. The current date is April 30 and his total gross revenues year to date are 300,500. There are other people on his team who have different start dates and different revenues year to date.

How would you structure the DB and calculate their commissions in SQL? Can you bring back one resultset, showing all users' commissions, year to date?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-09 : 07:42:02

Something like


Select Case 
	when grossRevenue between 0 and 100000 then grossRevenue*1.5 
	when grossRevenue between 100001 and 250000 then grossRevenue*1.55 
	when grossRevenue >2500001 then grossRevenue*1.6 end
from yourTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources