sql - calculate total salary based on employee type -
i want calculate salary each employee foe each month,
i have 2 tables , 2 views looks this
employees_view
| id | name | payrate | payunitcode | commission | |----|-------|---------|-------------|------------| | 1 | james | 10 | c | 0 | | 2 | mike | 10000 | s | 0 | | 3 | jude | 20000 | sc | 5 | | 4 | clara | 8 | c | 0 | jobs
| id | created | |----|---------------------| | 1 | 01/21/2016 10:56:05 | | 2 | 01/21/2016 10:56:05 | | 3 | 01/21/2016 10:56:05 | | 4 | 01/21/2016 10:56:05 | | 5 | 01/21/2016 12:11:59 | | 6 | 01/25/2016 08:03:07 | | 7 | 11/01/2015 22:55:22 | job_items_view
| job_id | amount | emp_id | |--------|--------|--------| | 1 | 135 | 4 | | 1 | 500 | 2 | | 3 | 1500 | 2 | | 3 | 250 | 4 | | 4 | 1000 | 2 | | 5 | 500 | 4 | | 6 | 500 | 4 | | 7 | 1000 | 1 | payunits
| code | name | |------|------------------------| | s | salary | | c | commission | | sc | salary plus commission | i have sql fiddle here data
when execute query
declare @startdatetime datetime = '2015-11-01 00:00:00' declare @enddatetime datetime = '2016-02-28 23:59:59' ;with sales ( select ev.id, isnull(sum(jiv.amount), 0) totalsales, month(j.created) [month], year(j.created) [year] employees_view ev left join job_items_view jiv on jiv.emp_id = ev.id left join jobs j on j.id = jiv.job_id j.created between @startdatetime , @enddatetime group ev.id, month(j.created), year(j.created) ), commissions ( select s.id, case ev.payunitcode when 'c' s.totalsales * (ev.payrate / 100) when 'sc' (select sum(amount) job_items_view) * (ev.commission / 100) else 0 end totalcommission sales s join employees_view ev on ev.id = s.id ), salaries ( select id, case payunitcode when 'c' 0 else payrate end salary employees_view ), totals ( select salaries.id, isnull(sales.month, month(@startdatetime)) [month], isnull(sales.year, year(@startdatetime)) [year], isnull(sales.totalsales, 0) totalsales, salaries.salary, isnull(commissions.totalcommission, 0) totalcommission salaries left join sales on salaries.id = sales.id left join commissions on commissions.id = sales.id ) select ev.payrate, ev.name, t.salary + t.totalcommission pay, left(datename(month, dateadd(month , t.[month], -1)), 3) + '-' + cast(t.[year] varchar) [month], ev.id emp_id, pu.name payunit, ev.commission totals t join employees_view ev on ev.id = t.id join payunits pu on pu.code = ev.payunitcode i get
| payrate | name | pay | month | emp_id | payunit | commission | |---------|-------|-------|----------|--------|------------------------|------------| | 10 | james | 100 | nov-2015 | 1 | commission | 0 | | 10000 | mike | 10000 | jan-2016 | 2 | salary | 0 | | 20000 | jude | 20000 | nov-2015 | 3 | salary plus commission | 5 | | 8 | clara | 110.8 | jan-2016 | 4 | commission | 0 | i expect get
| payrate | name | pay | month | emp_id | payunit | commission | |---------|-------|--------|----------|--------|------------------------|------------| | 10 | james | 100 | nov-2015 | 1 | commission | 0 | | 10000 | mike | 10000 | jan-2016 | 2 | salary | 0 | | 2000 | jude |20269.25| nov-2015 | 3 | salary plus commission | 5 | | 8 | clara | 110.8 | jan-2016 | 4 | commission | 0 | for payunit.code = c (commission) , pay = total sales * (employee.payrate/100)
for payunit.code = s (salary) , pay = employee.payrate
for payunit.code = sc (salary plus commission) , pay =employee.payrate + (total sales * (employee.commission/100))
take note of judes pay 20269.25 rather 20000 in previous table
this happens because exclude jude sales cte when filtering whole joined recordset period (despite left join) , no commission calculated against record in commissions cte. instead, should apply filter jobs subset this:
with sales ( select ev.id, isnull(sum(jiv.amount), 0) totalsales, month(j.created) [month], year(j.created) [year] employees_view ev left join job_items_view jiv on jiv.emp_id = ev.id left join (select * jobs created between @startdatetime , @enddatetime) j on j.id = jiv.job_id group ev.id, month(j.created), year(j.created) ) here updated sqlfiddle
Comments
Post a Comment