July 6, 2010 at 8:23 am
Hello,
I am trying to count distinct employees by month for each department. Below is my source table.
IF OBJECT_ID('TempDB..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table (Department VARCHAR(20), EmpID INT, [Month] INT)
INSERT INTO #Table
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,6 UNION ALL
SELECT 'Marketing',268638,6
The result set should look like this. IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (Department VARCHAR(20),[1] INT,[2] INT, [3] INT,[4] INT,[5] INT,[6] INT,[7] INT,[8] INT,[9] INT,[10] INT,[11] INT,[12] INT)
INSERT INTO #Results
SELECT 'Marketing',0,0,1,0,0,1,0,0,0,0,0,0
There is the same employee 268638 twice in the month of 3(March) and 6(June). I need to pivot the result set with distinct counts by month.
Any help please! Thanks.
July 6, 2010 at 8:57 am
You can pre-aggregate the results in the pivot source:
IF OBJECT_ID('TempDB..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table (Department VARCHAR(20), EmpID INT, [Month] INT)
INSERT INTO #Table
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,6 UNION ALL
SELECT 'Marketing',268638,6
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (Department VARCHAR(20), [1] INT,[2] INT, [3] INT,[4] INT,[5] INT,[6] INT,[7] INT,[8] INT,[9] INT,[10] INT,[11] INT,[12] INT)
INSERT INTO #Results
SELECT Department,
ISNULL([1],0),
ISNULL([2],0),
ISNULL([3],0),
ISNULL([4],0),
ISNULL([5],0),
ISNULL([6],0),
ISNULL([7],0),
ISNULL([8],0),
ISNULL([9],0),
ISNULL([10],0),
ISNULL([11],0),
ISNULL([12],0)
FROM (
SELECT Department, Month, CNT = COUNT(DISTINCT EmpId)
FROM #Table
GROUP BY Department, Month
) AS SRC
PIVOT (SUM(CNT) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT
SELECT * FROM #Results
Does it do the trick for you?
-- Gianluca Sartori
July 6, 2010 at 9:11 am
Perfect. Thank you so much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply