February 8, 2016 at 2:22 am
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 |
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
February 8, 2016 at 8:59 am
Take note of Judes pay which is 20269.25 rather than 20000 in the previous table
why should Judes pay be 20269.25...there were no sales for Jude (If I understand you correctly ??)
please post some scripts to set up your sample data as per http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 8, 2016 at 9:19 am
Hello
Fot Jude who os am SC (Salary plus commission) employee, he gets his montly salary plus 5% of the total sales
Pay = employee.Payrate + (total sales * (employee.Commission/100))
see an sql fiddle http://sqlfiddle.com/#!3/f4be2/1
Regards
February 8, 2016 at 9:22 am
coolcurrent4u (2/8/2016)
Hellosee an sql fiddle http://sqlfiddle.com/#!3/f4be2/1
Regards
no joy with that link...fails to load
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 8, 2016 at 9:23 am
coolcurrent4u (2/8/2016)
Hellosee an sql fiddle http://sqlfiddle.com/#!3/f4be2/1
Regards
You should post your stuff on this site for two reasons...
1. Your SQL Fiddle says "loading" and "Please build Schema". Its useless (for me, anyway).
2. SQL Fiddle could easily go away which would leave this thread incomplete. Please see the first link in my signature line below for how to get the best help.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2016 at 1:01 pm
I just tried the SQL Fiddle now, and its working. Maybe the server was down when you accessed it
February 8, 2016 at 1:13 pm
Is it that hard to post the code here?
create table Employees_View
(
ID int,
Name varchar(50),
PayRate float,
PayUnitCode varchar(2),
Commission float
)
insert Employees_View values
(1, 'James', 10, 'C', 0),
(2, 'Mike', 10000, 'S', 0),
(3, 'Jude', 20000, 'SC', 5),
(4, 'Clara', 8, 'C', 0)
create table Jobs
(
ID int,
Created datetime
)
insert Jobs values
(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')
create table Job_Items_View
(
Job_ID int,
Amount int,
Emp_ID int
)
insert Job_Items_View values
(1, 135, 4),
(1, 500, 2),
(3, 1500, 2),
(3, 250, 4),
(4, 1000, 2),
(5, 500, 4),
(6, 500, 4),
(7, 1000, 1)
create table PayUnits
(
[Code] varchar(2),
Name varchar(100)
)
insert PayUnits values
('S', 'Salary'),
('C', 'Commission'),
('SC', 'Salary plus Commission')
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
February 8, 2016 at 1:55 pm
coolcurrent4u (2/8/2016)
I just tried the SQL Fiddle now, and its working. Maybe the server was down when you accessed it
[off-topic-rant]
On that note, SQL Fiddle has been having some issues recently. I use it occasionally because I have a Chromebook, and don't always feel like connecting to my lab for testing. I've been getting gateway timeouts intermittently for weeks, and there was one week where it was down every time I tried.
I'm guessing it's something with their back-end SQL Server machines, because submitting code for other platforms like MySQL worked just fine.
[/off-topic-rant]
For a variety of reasons (the above included) it's more helpful for most people if you post the code in code tags here, for future reference 🙂
Anyway, if my understanding is correct, then the following should give you the result you want (at least for this data set and these rules):
DECLARE @startDateTime DATETIME = '2015-11-01 00:00:00';
DECLARE @endDateTime DATETIME = '2016-02-28 23:59:59';
WITH Sales AS
(
SELECT sales_amount=SUM(AMOUNT),
min_created=CONVERT(varchar(max),MIN(Created),107),
emp_id
FROM Job_Items_View JIV
INNER JOIN Jobs J ON J.ID=JIV.Job_ID
WHERE J.Created BETWEEN @startDateTime AND @endDateTime
GROUP BY ROLLUP (Emp_ID)
)
SELECT EV.PayRate,
EV.Name,
pay=CASE WHEN EV.PayUnitCode='S' THEN EV.PayRate
WHEN EV.PayUnitCode='C' THEN (EV.PayRate*S.sales_amount)/100.0
WHEN EV.PayUnitCode='SC' THEN EV.PayRate+((S.sales_amount*EV.Commission)/100.0)
END,
month_year=LEFT(min_created,3)+'-'+RIGHT(min_created,4),
Emp_ID=EV.ID,
PayUnits=PU.Name,
EV.Commission
FROM Employees_View EV
INNER JOIN Sales S ON EV.ID=S.Emp_ID OR (EV.PayUnitCode='SC' AND S.Emp_ID IS NULL)
INNER JOIN PayUnits PU ON PU.Code=EV.PayUnitCode
I didn't actually change your date comparison in the above, but I would suggest moving away from using BETWEEN to specify date ranges. You can use >= and < to specify date ranges instead. See http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx for a good explanation of that.
I hope this helps, and let us know if there are additional rules or data this doesn't cover.
Cheers!
EDIT: Tweaked some wording that seemed a bit ambiguous to me.
February 8, 2016 at 2:16 pm
Jacob's solution gives what you have asked for...however I am wondering how you hold your payrates?
currently you have a four month spread for your query with a single payrate for persons on salary......will this be the same if only ran for a single month?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 8, 2016 at 3:03 pm
I get this error
The CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode. They are only allowed in 100 mode or higher.
February 8, 2016 at 3:05 pm
Well this is the current solution i could come up with do you want to suggest a better solution?
February 8, 2016 at 3:16 pm
coolcurrent4u (2/8/2016)
I get this errorThe CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode. They are only allowed in 100 mode or higher.
That just means that you're on a 2008+ server, but the compatibility level for the database in which you're executing the query is lower than 100. A compatibility level of at least 100 is required to use ROLLUP().
You could change the compatibility level of the database in which you're testing it to allow the new syntax.
Alternatively, the query could be tweaked a bit to work on a lower compatibility level. I just used ROLLUP because it was convenient, since for this problem we want both per-individual sales as well as total sales across all individuals.
Getting the total sales without a ROLLUP wouldn't require much tweaking.
If changing the compatibility level of the DB isn't an option, then try your hand at altering my solution (after all, if you're going to be supporting this code, then hopefully you are able to understand it well enough to tweak it :-)).
If you run into some roadblocks, just let us know.
Cheers!
EDIT: I haven't had adequate coffee, so I left out the simplest option: just switch to the WITH ROLLUP syntax from 2005 🙂
February 8, 2016 at 3:33 pm
This should work the same way as the code from Jacob. This is the deprecated syntax.
DECLARE @startDateTime DATETIME = '2015-11-01 00:00:00';
DECLARE @endDateTime DATETIME = '2016-02-28 23:59:59';
WITH Sales AS
(
SELECT sales_amount=SUM(AMOUNT),
min_created=CONVERT(varchar(max),MIN(Created),107),
emp_id
FROM Job_Items_View JIV
INNER JOIN Jobs J ON J.ID=JIV.Job_ID
WHERE J.Created BETWEEN @startDateTime AND @endDateTime
GROUP BY Emp_ID WITH ROLLUP
)
SELECT EV.PayRate,
EV.Name,
pay=CASE WHEN EV.PayUnitCode='S' THEN EV.PayRate
WHEN EV.PayUnitCode='C' THEN (EV.PayRate*S.sales_amount)/100.0
WHEN EV.PayUnitCode='SC' THEN EV.PayRate+((S.sales_amount*EV.Commission)/100.0)
END,
month_year=LEFT(min_created,3)+'-'+RIGHT(min_created,4),
Emp_ID=EV.ID,
PayUnits=PU.Name,
EV.Commission
FROM Employees_View EV
INNER JOIN Sales S ON EV.ID=S.Emp_ID OR (EV.PayUnitCode='SC' AND S.Emp_ID IS NULL)
INNER JOIN PayUnits PU ON PU.Code=EV.PayUnitCode;
February 8, 2016 at 4:17 pm
When i use these two dates
DECLARE @startDateTime DATETIME = '2016-01-01 00:00:00';
DECLARE @endDateTime DATETIME = '2016-01-30 23:59:59';
I get these result
PayRateNamepaymonth_yearEmp_IDPayUnits Commission
10000.00Mike 10000.000000Jan-20163Salary 0.00
10000.00Mike 10000.000000Jan-20163Salary 0.00
20000.00Jude 20219.251500Jan-20165Salary plus Commission 5.00
8.00 Clara 240.002400 Jan-20162Commission 0.00
Mike appears twice instead of one
February 8, 2016 at 4:35 pm
When I run the WITH ROLLUP version of my query (the one Luis posted) against your sample data with those dates, I get the following:
| PayRate | Name | pay | month_year | Emp_ID | PayUnits | Commission |
|---------|-------|----------|------------|--------|------------------------|------------|
| 10000 | Mike | 10000 | Jan-2016 | 2 | Salary | 0 |
| 8 | Clara | 110.8 | Jan-2016 | 4 | Commission | 0 |
| 20000 | Jude | 20219.25 | Jan-2016 | 3 | Salary plus Commission | 5 |
Are you running this against different data now? If so, could you post the sample data that leads to this issue?
Cheers!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply