calculate total salary based on employee type

Question

I want to calculate the salary for each employee foe each month, so

I have two tables and two views that looks like 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 an SQL FIDDLE here with data

when i execute the query

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

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 to 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 which is 20269.25 rather than 20000 in the previous table

Answer 1

This happens because you exclude Jude from the sales CTE when filtering the whole joined recordset by period (despite the LEFT JOIN) and thus no commission is calculated against her record in the commissions CTE. Instead, you should apply the filter only to the jobs subset like this:

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 
  (select * from jobs WHERE Created BETWEEN @startDateTime AND @endDateTime) as j ON j.ID = jiv.Job_ID
  GROUP BY 
    ev.ID,
    MONTH(j.Created),
    YEAR(j.Created)
)

Here is the updated SQLFiddle