calculating employee commission

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

Transact-SQL (2000)

calculating employee commission

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-20 : 17:11:42

I have the following tables:

Table1:
Emp %Sales
1 2.3

Table2:
Project %Sales CommissionAmt
A 2.2 1.50
A 2.4 1.75

Employee1 has 2.3 % sales.
I have to figure out from Table 2 which commission amount to apply. Because emp1 has 2.3 I need to apply $1.50.

How do I do this in a query?

Thanks,
Ninel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-20 : 17:23:43

Well this gives me the correct answer, however I'm sure there is more to the story though...



DECLARE @Table1 table (Emp int, PercentSales decimal(2,1))
DECLARE @Table2 table (Project char(1), PercentSales decimal(2,1), CommissionAmt decimal(3,2))

INSERT INTO @Table1 VALUES(1, 2.3)
INSERT INTO @Table2 VALUES('A', 2.2, 1.50)
INSERT INTO @Table2 VALUES('A', 2.4, 1.75)

SELECT CommissionAmt
FROM @Table2
WHERE PercentSales <= (SELECT PercentSales FROM @Table1)

Perhaps if you gave us more sample data (using the code form I provided) and more detailed information, the answer might be what you are looking for.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-20 : 17:31:39

Well mine is purposefully incorrect as the poster didn't provide much information. Looking at your solution looks to be what the poster wants, however I was hoping he/she would provide more details so that we don't have to assume what they want.

[EDIT] This response was based upon Nathan's post, but it has been deleted.

Tara

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-20 : 18:44:05

Sorry guys, I posted duplicate somehow... and then deleted both when trying to delete the dupe! Anyways, here was what I posted and Tara is referring to.

As Tara mentioned, its a little unclear what you are looking for. This will give max of qualifying percentages.

declare @table1 table (emp int, sales_percentage decimal(10,2))
declare @table2 table (project char(1), sales_percentage decimal(10,2), commission_amt decimal(10,2))

insert into @table1 
	select 1, 2.3 union all
	select 2, 2.4

insert into @table2
	select 'a', 2.2, 1.50 union all
	select 'a', 2.4, 1.75


select max(t2.commission_amt)
from @table2 t2 inner join @table1 t1 
	on t2.sales_percentage <= t1.sales_percentage
where t1.emp = 1

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-20 : 21:53:38

Sorry if I didn't provide enough information.

The end result should give me the record with the $1.50 commission amount because the employee's %sales is 2.3 and not high enough to receive the 2.4 %sales commission amount of $1.75.

I can not hard code the employee numbers. Is there a way of doing this without hardcoding?

Thanks for all your help,
Ninel

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-20 : 22:04:00

Thank you Tara. You gave me just what I needed.

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-20 : 22:59:33

I have to add something to my request:

DECLARE @Table1 table (agentid int, sales_percentage decimal(10,2)decimal(10,2))
DECLARE @Table1 table (Project varchar(20), sales_percentage decimal(10,2) decimal(10,2), mcommissionamt decimal(10,2))
INSERT INTO @Table1 VALUES('1', 2.3)
INSERT INTO @Table1 VALUES('2', 2.4)

INSERT INTO @Table2 VALUES('A', 2.2, 1.50)
INSERT INTO @Table2 VALUES('A', 2.4, 1.75)
INSERT INTO @Table2 VALUES('B', 1.5, 1.00)
INSERT INTO @Table2 VALUES('B', 2.0, 1.25)

Result should be :
Emp project sales_percentage
1 A 1.50 -->Because emp1%(2.3) sales% didn't reach 2.4
2 B 1.25 -->Because emp2%(2.4) sales% went above the 2.0

I used Tara's query and that didn't work.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-20 : 23:53:07

It looks like you need to relate agentID to project... you need to clarify your requirements, only then can we help.

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-21 : 11:30:22

In this case it will be the max, but in other cases it won't.

For example:
Emp1 worked on project A and has % of 2.3
Emp2 worked on project B and has % of 2.1

Project A commissions at 2.2% for $1.50 and 2.4% for $1.75
Project B commissions at 1.5% for $1.00 and 2.0% for $1.25

Emp1 needs to receive the $1.50 commission (because his % didn't reach 2.4) while Emp2 needs to receive the $1.25 commission (because his % went above the 2.0%).

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-21 : 11:52:01

You still need to relate agents to projects, else how would you know what sales % to apply?

Check this setup, which allows agents to work on multiple projects, and for you to dictate multiple sales % per project:

set nocount on
declare @agent table (agent_id int, first_name varchar(10), last_name varchar(10))
declare @project table (project_id int, project_name varchar(20))
declare @project_commission table (project_id int, sales_percentage decimal(10,2), commission_amt decimal(10,2)) 
declare @agent_project table (agent_id int, project_id int, sales_percentage decimal(10,2))

insert into @agent values('1', 'nathan', 'skerl')
insert into @agent values('2', 'tara', 'duggan')

insert into @project values(1, 'project a')
insert into @project values(2, 'project b')

insert into @project_commission values (1, 2.2, 1.50)
insert into @project_commission values (1, 2.4, 1.75)
insert into @project_commission values (2, 1.5, 1.00)
insert into @project_commission values (2, 2.0, 1.25)

insert into @agent_project values (1, 1, 2.3)
insert into @agent_project values (2, 2, 2.1)


select a.agent_id, p.project_id, max(pc.commission_amt) as commission_amt
from @agent a inner join @agent_project ap 
	on a.agent_id = ap.agent_id inner join @project p
		on ap.project_id = p.project_id inner join @project_commission pc
			on p.project_id = pc.project_id
where pc.sales_percentage <= ap.sales_percentage
group by a.agent_id, p.project_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources