CODEWITHSUNDEEP
sqlsql-server-2008
Smith
Smith

Reputation: 5959

select group by column optionally in rows

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

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

Upvotes: 1

Views: 117

Answers (1)

davmos
davmos

Reputation: 9577

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.

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.

Upvotes: 1

Related Questions