calculate total salary based on employee type
<p>I want to calculate the salary for each employee foe each month, so </p>

<p>I have two tables and two views that looks like this</p>

<p>Employees_View</p>

<pre><code>| 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 |
</code></pre>

<p>Jobs</p>

<pre><code>| 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 |
</code></pre>

<p>Job_Items_View</p>

<pre><code>| 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 |
</code></pre>

<p>PayUnits</p>

<pre><code>| Code |                   Name |
|------|------------------------|
|    S |                 Salary |
|    C |             Commission |
|   SC | Salary plus Commission |
</code></pre>

<p>I have an <a href="http://sqlfiddle.com/#!3/f4be2/1" rel="nofollow"><strong>SQL FIDDLE</strong></a> here with data</p>

<p>when i execute the query</p>

<pre><code>DECLARE @startDateTime DATETIME = '2015-11-01 00:00:00'
DECLARE @endDateTime DATETIME = '2016-02-28 23:59:59'

;WITH sales AS
(
  SELECT 
    ev.ID,
    ISNULL(SUM(jiv.Amount), 0) AS TotalSales,
    MONTH(j.Created) AS [Month],
    YEAR(j.Created) AS [Year]
  FROM Employees_View AS ev
  LEFT JOIN Job_Items_View AS jiv ON jiv.Emp_ID = ev.ID
  LEFT JOIN Jobs AS j ON j.ID = jiv.Job_ID
  WHERE j.Created BETWEEN @startDateTime AND @endDateTime
  GROUP BY 
    ev.ID,
    MONTH(j.Created),
    YEAR(j.Created)
),
commissions AS
(
  SELECT
    s.ID,
    CASE ev.PayUnitCode 
      WHEN 'C' THEN s.TotalSales * (ev.PayRate / 100)   
      WHEN 'SC' THEN (SELECT SUM(Amount) FROM Job_Items_View) * (ev.Commission / 100)
      ELSE 0
    END AS TotalCommission
  FROM sales AS s
  JOIN Employees_View AS ev ON ev.ID = s.ID
),
salaries AS
(
  SELECT 
    ID, 
    CASE PayUnitCode 
      WHEN 'C' THEN 0 
      ELSE PayRate 
    END AS Salary 
  FROM Employees_View 
),
totals AS
(
  SELECT 
    salaries.ID,
    ISNULL(sales.Month, MONTH(@startDateTime)) AS [Month],
    ISNULL(sales.Year, YEAR(@startDateTime)) AS [Year],
    ISNULL(sales.TotalSales, 0) AS TotalSales,
    salaries.Salary,
    ISNULL(commissions.TotalCommission, 0) AS TotalCommission
  FROM 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 AS Pay,
  LEFT(DATENAME(MONTH, DATEADD(MONTH , t.[Month], -1)), 3) 
    + '-' + CAST(t.[Year] AS VARCHAR) AS [Month],
  ev.ID AS Emp_ID,
  pu.Name AS PayUnit,
  ev.Commission
FROM totals AS t
JOIN Employees_View AS ev ON ev.ID = t.ID
JOIN PayUnits AS pu ON pu.Code = ev.PayUnitCode
</code></pre>

<p>I get</p>

<pre><code>| 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 |
</code></pre>

<p>I expect to get</p>

<pre><code>| 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 |
</code></pre>

<p>For PayUnit.Code = C (Commission) , Pay = total sales * (employee.Payrate/100)</p>

<p>For PayUnit.Code  = S (Salary) , Pay = employee.Payrate</p>

<p>For PayUnit.Code = SC (Salary plus commission) , Pay =employee.Payrate + (total sales * (employee.Commission/100))</p>

<p>Take note of Judes pay which is 20269.25 rather than 20000 in the previous table</p>