Smith February 2016

calculate total salary based on employee type

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.Crea        

Answers


nanestev February 2016

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

Post Status

Asked in February 2016
Viewed 1,953 times
Voted 6
Answered 1 times

Search




Leave an answer