Calculate monthly salary for employees with different pay type

Transact-SQL
1

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 |  0.00  | Nov-2015 |      2 |                 Salary |          0 |
|   20000 |  Jude |20050.00| Nov-2015 |      3 | Salary plus Commission |          5 |
|      10 | James |   0.0  | Dec-2015 |      1 |             Commission |          0 |
|   10000 |  Mike |  0.00  | Dec-2015 |      2 |                 Salary |          0 |
|   20000 |  Jude |20000.00| Dec-2015 |      3 | Salary plus Commission |          5 |
|       8 | Clara |  0.00  | Dec-2016 |      4 |             Commission |          0 |
|      10 | James |  0.00  | Jan-2016 |      1 |             Commission |          0 |
|   10000 |  Mike | 10000  | Jan-2016 |      2 |                 Salary |          0 |
|   20000 |  Jude |20219.25| Jan-2016 |      3 | Salary plus Commission |          5 |
|       8 | Clara | 110.8  | Jan-2016 |      4 |             Commission |          0 |
|      10 | James |    0.00| Feb-2015 |      1 |             Commission |          0 |
|   10000 |  Mike |10000.00| Feb-2015 |      2 |                 Salary |          0 |
|   20000 |  Jude |20000.00| Feb-2015 |      3 | Salary plus Commission |          5 |
|       8 | Clara |    0.00| Feb-2015 |      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

2

can you explain how the expected result comes about ? For example, James, why there is a record for Nov-2015 and Jan-2016 ?

3

Your expected output seems off.

  • Why would James get payed 100 in Nov and Jan (he only worked in Nov)?
  • Why would Jude get commission for the Whole 4 months in Nov and Jan?
  • Why would Clare get commission in Nov when she only worked in Jan?
  • Doesn't Mike and Clara get payed in Dec and Feb?

My suggestion is, that you're lokking for this output:

| PayRate |  Name |   Pay    |    Month | Emp_ID |                PayUnit | Commission |
|---------|-------|----------|----------|--------|------------------------|------------|
|      10 | James |   100.00 | Nov-2015 |      1 |             Commission |          0 |
|   10000 |  Mike | 10000.00 | Nov-2015 |      2 |                 Salary |          0 |
|   20000 |  Jude | 20050.00 | Nov-2015 |      3 | Salary plus Commission |          5 |
|       8 | Clara |     0.00 | Nov-2015 |      4 |             Commission |          0 |
|      10 | James |     0.00 | Dec-2015 |      1 |             Commission |          0 |
|   10000 |  Mike | 10000.00 | Dec-2015 |      2 |                 Salary |          0 |
|   20000 |  Jude | 20000.00 | Dec-2015 |      3 | Salary plus Commission |          5 |
|       8 | Clara |     0.00 | Dec-2015 |      4 |             Commission |          0 |
|      10 | James |     0.00 | Jan-2016 |      1 |             Commission |          0 |
|   10000 |  Mike | 10000.00 | Jan-2016 |      2 |                 Salary |          0 |
|   20000 |  Jude | 20219.25 | Jan-2015 |      3 | Salary plus Commission |          5 |
|       8 | Clara |   110.80 | Jan-2016 |      4 |             Commission |          0 |
|      10 | James |     0.00 | Feb-2015 |      1 |             Commission |          0 |
|   10000 |  Mike | 10000.00 | Feb-2015 |      2 |                 Salary |          0 |
|   20000 |  Jude | 20000.00 | Feb-2015 |      3 | Salary plus Commission |          5 |
|       8 | Clara |     0.00 | Feb-2015 |      4 |             Commission |          0 |
1 Like
4

Yes this is correct

5

Perhaps this:

declare @start_date date='2015-11-01';
declare @end_date date='2016-03-01';

with tallyno10(n)
  as (select 0 from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tallyno10(n))

    ,tallyno1000(n)
  as (select row_number() over(order by (select null))-1
        from tallyno10 as a
             cross apply tallyno10 as b
             cross apply tallyno10 as c
     )

    ,tallydatetime(start_date,end_date)
  as (select cast(dateadd(month,n,@start_date) as datetime)
            ,cast(dateadd(month,n+1,@start_date) as datetime)
        from tallyno1000 as a
       where @end_date>dateadd(month,n,@start_date)
     )

    ,sale(start_date,amount)
  as (select dt.start_date
            ,sum(ji.amount) as amount
        from tallydatetime as dt
             inner join jobs as j
                     on j.created>=dt.start_date
                    and j.created<dt.end_date
             inner join job_items_view as ji
                     on ji.job_id=j.id
       group by dt.start_date
     )

select e.payrate
      ,e.name
      ,avg(case when e.payunitcode in ('S','SC') then e.payrate else 0 end)
      +sum(case
              when e.payunitcode='S'
            or (e.payunitcode='C'
           and  j.id is null
               )
              then 0
              when e.payunitcode='C'
              then coalesce(ji.amount,0)*e.payrate/100
              when e.payunitcode='SC'
              then coalesce(s.amount,0)*e.commission/100
              else 0
           end
          )
       as pay
      ,left(datename(month,dt.start_date),3)+'-'+datename(year,dt.start_date) as [month]
      ,e.id as emp_id
      ,pu.name as payunit
      ,e.commission
  from employees_view as e
       cross apply tallydatetime as dt
       left outer join sale as s
                    on s.start_date=dt.start_date
       left outer join job_items_view as ji
                    on ji.emp_id=e.id
       left outer join jobs as j
                    on j.id=ji.job_id
                   and j.created>=dt.start_date
                   and j.created<dt.end_date
       left outer join payunits as pu
                    on pu.code=e.payunitcode
 group by e.payrate
         ,e.name
         ,left(datename(month,dt.start_date),3)+'-'+datename(year,dt.start_date)
         ,e.id
         ,pu.name
         ,e.commission
         ,dt.start_date
 order by dt.start_date
         ,e.id
;