January 3, 2017 at 10:33 am
I have a table that stores EmployeeID, and Date. I want to create a report for 2016 that shows what the best month, by highest record count, for each employee. I have no idea how to accomplish this. I've been fiddling around with counts, but I'm not even close.
Basically, I'm looking for results like:
EmployeeID BestMonth
10000 May
10101 December
12345 June
If I can get the number of the month, I can take care of making it look pretty. I just don't have any idea how to get counts for each EmployeeID per month.
Any help would be, well, helpful.
January 3, 2017 at 12:01 pm
jtrask (1/3/2017)
I have a table that stores EmployeeID, and Date. I want to create a report for 2016 that shows what the best month, by highest record count, for each employee. I have no idea how to accomplish this. I've been fiddling around with counts, but I'm not even close.Basically, I'm looking for results like:
EmployeeID BestMonth
10000 May
10101 December
12345 June
If I can get the number of the month, I can take care of making it look pretty. I just don't have any idea how to get counts for each EmployeeID per month.
Any help would be, well, helpful.
There's a number of ways you could accomplish this. What have you tried so far? Do you have a calendar table you're working with? What indexes are on this table?
January 3, 2017 at 12:38 pm
jtrask (1/3/2017)
I have a table that stores EmployeeID, and Date. I want to create a report for 2016 that shows what the best month, by highest record count, for each employee. I have no idea how to accomplish this. I've been fiddling around with counts, but I'm not even close.Basically, I'm looking for results like:
EmployeeID BestMonth
10000 May
10101 December
12345 June
If I can get the number of the month, I can take care of making it look pretty. I just don't have any idea how to get counts for each EmployeeID per month.
Any help would be, well, helpful.
First order of business, you need to know exactly which field determines which month a given EmployeeID value belongs to. I'll call that field THE_DATE in the following query, where the table it's contained in; along with the EmployeeID field; I'll call YOUR_TABLE:
CREATE TABLE dbo.YOUR_TABLE (
EmployeeID int NOT NULL,
THE_DATE date
);
INSERT INTO dbo.YOUR_TABLE
VALUES(10000, '2016-01-01'),
(10000, '2016-05-01'),
(10000, '2016-05-02'),
(10000, '2016-05-03'),
(10000, '2016-05-04'),
(10000, '2016-05-11'),
(10000, '2016-05-12'),
(10000, '2016-05-21'),
(10000, '2016-05-31'),
(10101, '2016-05-01'),
(10101, '2016-12-01'),
(10101, '2016-12-02'),
(10101, '2016-12-03'),
(10101, '2016-12-07'),
(10101, '2016-12-11'),
(10101, '2016-12-12'),
(12345, '2016-01-01'),
(12345, '2016-06-01'),
(12345, '2016-06-02'),
(12345, '2016-06-03'),
(12345, '2016-06-04'),
(12345, '2016-06-07');
WITH COUNTS AS (
SELECT EmployeeID, YEAR(THE_DATE) AS THE_YEAR, MONTH(THE_DATE) AS THE_MONTH, COUNT(*) AS THE_COUNT
FROM YOUR_TABLE
GROUP BY EmployeeID, YEAR(THE_DATE), MONTH(THE_DATE)
),
MAX_COUNTS AS (
SELECT EmployeeID, MAX(THE_COUNT) AS MAX_COUNT
FROM COUNTS
GROUP BY EmployeeID
)
SELECT C.EmployeeID, CAST(C.THE_YEAR AS char(4)) + '-' + DATENAME(month, DATEFROMPARTS(C.THE_YEAR, C.THE_MONTH, 1)) AS MONTH_NAME
FROM COUNTS AS C
INNER JOIN MAX_COUNTS AS MC
ON C.EmployeeID = MC.EmployeeID
AND C.THE_COUNT = MC.MAX_COUNT
ORDER BY C.EmployeeID;
Let me know if that works for you...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 3, 2017 at 12:51 pm
So what you want is the MAX(COUNT) of sales for each (e.g. "GROUP BY") EmployeeID by Month and Year. Let's start with some sample data; since you did not provide any DDL. Please correct my assumptions if needed.
DECLARE @sale TABLE (EmployeeID int, saledate date);
INSERT @sale
SELECT TOP(30) 1000, DATEADD(DAY, checksum(newid())%150, '20160601') FROM sys.all_columns
UNION ALL
SELECT TOP(40) 10101, DATEADD(DAY, checksum(newid())%150, '20160601') FROM sys.all_columns
UNION ALL
SELECT TOP(60) 12345, DATEADD(DAY, checksum(newid())%150, '20160601') FROM sys.all_columns;
SELECT * FROM @sale
This gives us:
EmployeeID saledate
----------- ----------
10101 2016-08-06
12345 2016-02-08
1000 2016-10-20
10101 2016-08-23
1000 2016-02-15
10101 2016-04-24
......
If you could do this you'd be all set:
SELECT
employeeID,
saleYear = YEAR(saledate),
saleMonth = MONTH(saledate),
totalSales = MAX(COUNT(*))
FROM @sale
GROUP BY EmployeeID, YEAR(saledate), MONTH(saledate)
This wont work because you can't nest aggregates like this so we'll have to break out the RANK Window Function like so (including sample data so you can just copy/paste and see what I'm doing):
DECLARE @sale TABLE (EmployeeID int, saledate date);
INSERT @sale
SELECT TOP(30) 1000, DATEADD(DAY, checksum(newid())%150, '20160601') FROM sys.all_columns
UNION ALL
SELECT TOP(40) 10101, DATEADD(DAY, checksum(newid())%150, '20160601') FROM sys.all_columns
UNION ALL
SELECT TOP(60) 12345, DATEADD(DAY, checksum(newid())%150, '20160601') FROM sys.all_columns;
SELECT
employeeID,
saleYear,
saleMonth,
totalSales
FROM
(
SELECT
employeeID,
saleYear = YEAR(saledate),
saleMonth = MONTH(saledate),
totalSales = COUNT(*),
rnk = RANK() OVER (PARTITION BY employeeID ORDER BY COUNT(*) DESC)
FROM @sale
GROUP BY EmployeeID, YEAR(saledate), MONTH(saledate)
) salesRank
WHERE rnk = 1;
Results:
employeeID saleYear saleMonth totalSales
----------- ----------- ----------- -----------
1000 2016 6 6
10101 2016 9 10
12345 2016 2 12
-- Itzik Ben-Gan 2001
January 3, 2017 at 1:16 pm
Alan.B (1/3/2017)
This wont work because you can't nest aggregates like this so we'll have to break out the RANK Window Function like so (including sample data so you can just copy/paste and see what I'm doing):
I'm guessing that you probably want to use the ROW_NUMBER function instead of the RANK function, because RANK will return ties, but ROW_NUMBER will never return ties.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 1:19 pm
Thank you so much. This was so far beyond my current, and possibly future, abilities. I was able to get as far as creating query that would give me EmployeeID and a total based on date, but that was it.
Hopefully I'll be able to use this is a basis for future project. The more I use and tweak it, the more I'll understand it.
January 3, 2017 at 1:53 pm
drew.allen (1/3/2017)
Alan.B (1/3/2017)
This wont work because you can't nest aggregates like this so we'll have to break out the RANK Window Function like so (including sample data so you can just copy/paste and see what I'm doing):I'm guessing that you probably want to use the ROW_NUMBER function instead of the RANK function, because RANK will return ties, but ROW_NUMBER will never return ties.
Drew
I did did not know how the OP wanted to handle ties so I went with RANK in case they wanted something like this (eg 10 sales for 10101 in 9-2016 and 11-2016):
employeeID saleYear saleMonth totalSales
----------- ----------- ----------- -----------
1000 2016 6 6
10101 2016 9 10
10101 2016 11 10
12345 2016 2 12
I was going for a TOP 1 WITH TIES (per group) solution.
-- Itzik Ben-Gan 2001
January 3, 2017 at 2:25 pm
Alan.B (1/3/2017)
drew.allen (1/3/2017)
Alan.B (1/3/2017)
This wont work because you can't nest aggregates like this so we'll have to break out the RANK Window Function like so (including sample data so you can just copy/paste and see what I'm doing):I'm guessing that you probably want to use the ROW_NUMBER function instead of the RANK function, because RANK will return ties, but ROW_NUMBER will never return ties.
Drew
I did did not know how the OP wanted to handle ties so I went with RANK in case they wanted something like this (eg 10 sales for 10101 in 9-2016 and 11-2016):
employeeID saleYear saleMonth totalSales----------- ----------- ----------- -----------
1000 2016 6 6
10101 2016 9 10
10101 2016 11 10
12345 2016 2 12
I was going for a TOP 1 WITH TIES (per group) solution.
In my experience, including ties generally confuses the people who are the typical consumers of this type of report, so I prefer the method that will produce unique records per group unless I'm specifically told to include ties.
In either case, the OP has the information he needs to decide between these two cases.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply