按行选择按行选择

I need to calculate employee salaries.

我需要计算员工工资。

I have a table and two views which holds data i need to perform a query, here are the tables

我有一个表和两个视图,其中包含我需要执行查询的数据,这里是表

Employees_View
--------
ID    Name    PayRate    PayUnit    Commission
1    James    10         C          0
2    Mike     10000      S          0
3    Jude     20000      SC         5
4    Clara    8          C          0

When PayUnit is C (Commission) then PayRate is in Percent, this is percent of the total sale by this employee, whereas the Commission field is commission percent on total sales and its only for SC employees

当PayUnit为C(佣金)时,PayRate为百分比,这是该员工总销售额的百分比,而佣金字段是总销售额的佣金百分比,仅适用于SC员工

Jobs

ID    Created
1     2016-01-21 10:56:05
2     2016-01-21 10:56:05
3     2016-01-21 10:56:05
4     2016-01-21 10:56:05
5     2016-01-21 12:11:59
6     2016-01-25 08:03:07
7     2015-11-01 22:55:22

Jobs_Item_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        500         1

PayUnits

Code    Name
S       Salary   
C       Commission
SC      Salary plus Commission

Here is what i tried

这是我试过的

SELECT 
    ev.PayRate, 
    ev.name AS Employee, 
    CASE ev.PayUnitCode WHEN 'C' THEN 
        SUM(jiv.Amount) - (SUM(jiv.Amount) * (ev.PayRate / 100))  
    WHEN 'SC' THEN 
        ev.payrate + SUM(jiv.Amount) - (SUM(jiv.Amount) * (ev.Commission / 100))
    ELSE ev.payrate 
    END AS pay,
    LEFT(DATENAME(month, j.Created), 3) + '-' + CAST(YEAR(j.Created) AS NVARCHAR) AS Month, 
    jiv.Emp_ID, 
    pu.Name AS PayUnit, 
     ev.Code, ev.Commission
FROM 
    dbo.Employees_View AS ev LEFT OUTER JOIN
    dbo.Job_Items_View AS jiv  ON jiv.Emp_ID = ev.ID LEFT OUTER JOIN
    dbo.Jobs AS j ON j.ID = jiv.Job_ID LEFT OUTER JOIN
    dbo.PayUnits AS pu ON pu.Code = ev.PayUnitCode

GROUP BY jiv.Emp_ID, 
        pu.Name, 
        ev.PayUnitCode,
        ev.PayRate, ev.name, 
        LEFT(DATENAME(month, j.Created), 3) + '-' + CAST(YEAR(j.Created) AS NVARCHAR), 
        ev.Code, ev.Commission

This is what i got

这就是我得到的

Result

PayRate Employee pay   Month    Emp_ID  PayUnit                 Commission
20000   Jude    NULL   NULL     NULL    Salary plus Commission  5.00
10      James   900    Nov-2015 1       Commission              0.00
8       Clara   2760   Jan-2016 4       Commission              0.00
10000   Mike    10000  Jan-2016 2       Salary                  0.00

Expected Output

Result

PayRate Employee pay     Month  Emp_ID  PayUnit                 Commission
20000   Jude    20241.75 Jan-2016   3       Salary plus Commission  5.00
10      James   900      Nov-2015   1       Commission              0.00
8       Clara   2760     Jan-2016   4       Commission              0.00
10000   Mike    10000    Jan-2016   2       Salary                  0.00

the Pay for the SC employee isn't correct. it is suppose to be Salary (20000) plus 5% or total sales (4835) which is 241.75 - 20,241.75

SC员工的薪酬不正确。假设是薪水(20000)加上5%或总销售额(4835),即241.75 - 20,241.75

1 个解决方案

#1

The reason for the nulls is that one of the employees did not contribute to any jobs, so SUM(jiv.Amount) will always be null for them and any calculations involving that expression will also result in null. You can fix by doing ISNULL(SUM(jiv.Amount), 0). Similarly for the dates, they are also driven by the jobs data, but if an employee wasn't involved in any jobs they will be null also. ISNULL() could be used for these as well.

空值的原因是其中一个员工没有为任何作业做出贡献,因此对于他们,SUM(jiv.Amount)将始终为null,并且涉及该表达式的任何计算也将导致null。您可以通过执行ISNULL(SUM(jiv.Amount),0)来修复。类似地,对于日期,它们也由作业数据驱动,但如果员工没有参与任何工作,他们也将为空。 ISNULL()也可以用于这些。

I've broken the problem down by using Common Table Expressions:

我通过使用公用表表达式来解决问题:

DECLARE @startDateTime DATETIME = '2016-01-01 00:00:00'
DECLARE @endDateTime DATETIME = '2016-01-31 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

Click here to see it in action and have a play on SQL Fiddle.

点击这里查看它的实际效果并在SQL Fiddle上播放。