Calculate number of employees in a month (crosstab)
-
Hi,
I have following data about employees:
EmployeeID, HireDate, TerminationDate
Based on that data I need to calculate how many employees was hired in a month, how many of them are terminated and total number of employees.
Note: When TerminationDate column has NULL value, it means employee still works in a company.
User selects period - Month and Year, e.g. Jan 2007 - May 2007 for which he wants data to be retrieved and the final outcome (report) should be something like this:
Jan Feb Mar Apr May
Hired 3 0 2 0 0
Terminated 0 1 0 1 0
# of Employees 3 3 4 4 3
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[HireDate] [datetime] NOT NULL,
[TerminationDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO Employees (HireDate) VALUES ('Jan 1 2007')
INSERT INTO Employees (HireDate) VALUES ('Jan 5 2007')
INSERT INTO Employees (HireDate) VALUES ('Mar 1 2007')
INSERT INTO Employees (HireDate, TerminationDate) VALUES ('Jan 1 2007', 'Feb 1 2007')
INSERT INTO Employees (HireDate, TerminationDate) VALUES ('Mar 1 2007', 'Apr 30 2007')
My question is: how to count those three categories of employees for each month in specified period even when there are no changes in a month?
Period requested by user could span over multiple years.
Thanks!
-
Using a calendar table such as found here
you can query for the number of employees hired, terminated and total per month using the query below.
There are plenty of articles on how to rotate the data (search for "pivot") when there are an unknown number of columns (months in your case)
DECLARE @PeriodStart DATETIME
DECLARE @PeriodEnd DATETIME
SET @PeriodStart='1 Jan 2007'
SET @PeriodEnd='31 May 2007'
SELECT c.Y,c.M,
COUNT(DISTINCT CASE WHEN DATEPART(Year,e.HireDate)=c.Y AND DATEPART(Month,e.HireDate)=c.M THEN e.EmployeeID END) AS Hired,
COUNT(DISTINCT CASE WHEN DATEPART(Year,e.TerminationDate)=c.Y AND DATEPART(Month,e.TerminationDate)=c.M THEN e.EmployeeID END) AS Terminated,
COUNT(DISTINCT e.EmployeeID) AS [# of Employees]
FROM Calendar c
LEFT OUTER JOIN Employees e ON c.dt BETWEEN e.HireDate AND COALESCE(e.TerminationDate,c.dt)
WHERE c.dt BETWEEN @PeriodStart AND @PeriodEnd
GROUP BY c.Y,c.M
ORDER BY c.Y,c.M
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
THANKS A LOT!!!!
-
This method uses pivot tables:
/*
==========================
Expected Output
==========================
Jan Feb Mar Apr May
Hired 3 0 2 0 0
Terminated 0 1 0 1 0
# of Employees 3 3 4 4 3
*/
DECLARE @Employees TABLE
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[HireDate] [datetime] NOT NULL,
[TerminationDate] [datetime] NULL
)
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('Jan 1 2007','Jan 2 2007')
INSERT INTO @Employees (HireDate) VALUES ('Jan 5 2007')
INSERT INTO @Employees (HireDate) VALUES ('Mar 1 2007')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('Jan 1 2007', 'Feb 1 2007')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('Mar 1 2007', 'Apr 30 2007')
--table var to hold results
DECLARE @RESULTS TABLE
(
[Type] varchar(20),
[JAN] INT,
[FEB] INT,
[MAR] INT,
[APR] INT,
[MAY] INT,
[JUN] INT,
[JUL] INT,
[AUG] INT,
[SEP] INT,
[OCT] INT,
[NOV] INT,
[DEC] INT
)
--================================================
--Start HireDate pivot
--================================================
INSERT INTO @RESULTS
Select 'HIRED' AS [HIRED],
--list columns for pivot
ISNULL([JAN] , 0) AS [JAN],
ISNULL([FEB] , 0) AS [FEB],
ISNULL([MAR] , 0) AS [MAR],
ISNULL([APR] , 0) AS [APR],
ISNULL([MAY] , 0) AS [MAY],
ISNULL([JUN] , 0) AS [JUN],
ISNULL([JUL] , 0) AS [JUL],
ISNULL([AUG] , 0) AS [AUG],
ISNULL([SEP] , 0) AS [SEP],
ISNULL([OCT] , 0) AS [OCT],
ISNULL([NOV] , 0) AS [NOV],
ISNULL([DEC] , 0) AS [DEC]
FROM(SELECT
--take the 3 first characters of the month name for grouping
LEFT(UPPER(DATENAME(MONTH, HireDate)),3) AS [HireDate]
FROM
@Employees
--where filter for current year data only
WHERE DATEPART(yyyy,TerminationDate) = '2007'
) AS dataToPivotTable
PIVOT
(COUNT(HireDate) -- value to aggregate
FOR HireDate -- what are your horizontal columns (fields)
IN ( [JAN]
,[FEB]
,[MAR]
,[APR]
,[MAY]
,[JUN]
,[JUL]
,[AUG]
,[SEP]
,[OCT]
,[NOV]
,[DEC]
)
) AS whatToPivotOnTable
--================================================
--End HireDate pivot
--================================================
--================================================
--Start TerminationDate pivot
--================================================
INSERT INTO @RESULTS
Select 'TERMINATED' AS [TERMINATED],
--list columns for pivot
ISNULL([JAN] , 0) AS [JAN],
ISNULL([FEB] , 0) AS [FEB],
ISNULL([MAR] , 0) AS [MAR],
ISNULL([APR] , 0) AS [APR],
ISNULL([MAY] , 0) AS [MAY],
ISNULL([JUN] , 0) AS [JUN],
ISNULL([JUL] , 0) AS [JUL],
ISNULL([AUG] , 0) AS [AUG],
ISNULL([SEP] , 0) AS [SEP],
ISNULL([OCT] , 0) AS [OCT],
ISNULL([NOV] , 0) AS [NOV],
ISNULL([DEC] , 0) AS [DEC]
FROM(SELECT
--take the 3 first characters of the month name for grouping
LEFT(UPPER(DATENAME(MONTH, TerminationDate)),3) AS [TerminationDate]
FROM
@Employees
--where filter for current year data only
WHERE DATEPART(yyyy,TerminationDate) = '2007'
) AS dataToPivotTable
PIVOT
(COUNT(TerminationDate) -- value to aggregate
FOR TerminationDate -- what are your horizontal columns (fields)
IN ( [JAN]
,[FEB]
,[MAR]
,[APR]
,[MAY]
,[JUN]
,[JUL]
,[AUG]
,[SEP]
,[OCT]
,[NOV]
,[DEC]
)
) AS whatToPivotOnTable
--================================================
--End TerminationDate pivot
--================================================
--================================================
--Start Insert Summation By Month
--================================================
INSERT INTO @RESULTS
SELECT '# Employees',
SUM([JAN]) AS [JAN],
SUM([FEB]) AS [FEB],
SUM([MAR]) AS [MAR],
SUM([APR]) AS [APR],
SUM([MAY]) AS [MAY],
SUM([JUN]) AS [JUN],
SUM([JUL]) AS [JUL],
SUM([AUG]) AS [AUG],
SUM([SEP]) AS [SEP],
SUM([OCT]) AS [OCT],
SUM([NOV]) AS [NOV],
SUM([DEC]) AS [DEC]
FROM @RESULTS
--================================================
--End Insert Summation By Month
--================================================
--================================================
--View results
--================================================
SELECT *
FROM @RESULTS
-
Pivot Tables limit what you can do dynamically, but I have modified my original post to meet your specification of displaying the data and having the user put in a range.
Note: This is only good when dates do not overlap. Meaning you cannot run a this query with a begin date of 12/31/2006 and a end date of 12/31/2007. YOu should provide the script 1/1/2007 to 12/31/2007. The script will just aggregate the totals because it sees a value in December, not paying attention to the year.
MarkC's solution is a good one, this is just another perspective to look at.
/*
==========================
Expected Output
==========================
Jan Feb Mar Apr May
Hired 3 0 2 0 0
Terminated 0 1 0 1 0
# of Employees 3 3 4 4 3
*/
DECLARE @begin smalldatetime
DECLARE @end smalldatetime
--set range of time
Set @begin = '12/1/2006'
set @end = '3/30/2007'
DECLARE @Employees TABLE
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[HireDate] [datetime] NOT NULL,
[TerminationDate] [datetime] NULL
)
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('Jan 1 2007','Jan 2 2007')
INSERT INTO @Employees (HireDate) VALUES ('Jan 5 2007')
INSERT INTO @Employees (HireDate) VALUES ('DEC 5 2006')
INSERT INTO @Employees (HireDate) VALUES ('DEC 5 2006')
INSERT INTO @Employees (HireDate) VALUES ('DEC 5 2007')
INSERT INTO @Employees (HireDate) VALUES ('Mar 1 2007')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('Jan 1 2007', 'Feb 1 2007')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('Mar 1 2007', 'Apr 30 2007')
--table var to hold results
DECLARE @RESULTS TABLE
(
[Type] varchar(20),
[JAN] INT,
[FEB] INT,
[MAR] INT,
[APR] INT,
[MAY] INT,
[JUN] INT,
[JUL] INT,
[AUG] INT,
[SEP] INT,
[OCT] INT,
[NOV] INT,
[DEC] INT
)
--================================================
--Start HireDate pivot
--================================================
INSERT INTO @RESULTS
Select 'HIRED' AS [HIRED],
--list columns for pivot
ISNULL([JAN] , 0) AS [JAN],
ISNULL([FEB] , 0) AS [FEB],
ISNULL([MAR] , 0) AS [MAR],
ISNULL([APR] , 0) AS [APR],
ISNULL([MAY] , 0) AS [MAY],
ISNULL([JUN] , 0) AS [JUN],
ISNULL([JUL] , 0) AS [JUL],
ISNULL([AUG] , 0) AS [AUG],
ISNULL([SEP] , 0) AS [SEP],
ISNULL([OCT] , 0) AS [OCT],
ISNULL([NOV] , 0) AS [NOV],
ISNULL([DEC] , 0) AS [DEC]
FROM (SELECT
--take the 3 first characters of the month name for grouping
LEFT(UPPER(DATENAME(MONTH, HireDate)),3) AS [HireDate]
FROM
@Employees
--where filter for data between BOM and EOM
WHERE HireDate between DateAdd(month, DateDiff(month, 0, @begin), 0) and dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@end)+1, 0))
) AS dataToPivotTable
PIVOT
(COUNT(HireDate) -- value to aggregate
FOR HireDate -- what are your horizontal columns (fields)
IN ( [JAN]
,[FEB]
,[MAR]
,[APR]
,[MAY]
,[JUN]
,[JUL]
,[AUG]
,[SEP]
,[OCT]
,[NOV]
,[DEC]
)
) AS whatToPivotOnTable
--================================================
--End HireDate pivot
--================================================
--================================================
--Start TerminationDate pivot
--================================================
INSERT INTO @RESULTS
Select 'TERMINATED' AS [TERMINATED],
--list columns for pivot
ISNULL([JAN] , 0) AS [JAN],
ISNULL([FEB] , 0) AS [FEB],
ISNULL([MAR] , 0) AS [MAR],
ISNULL([APR] , 0) AS [APR],
ISNULL([MAY] , 0) AS [MAY],
ISNULL([JUN] , 0) AS [JUN],
ISNULL([JUL] , 0) AS [JUL],
ISNULL([AUG] , 0) AS [AUG],
ISNULL([SEP] , 0) AS [SEP],
ISNULL([OCT] , 0) AS [OCT],
ISNULL([NOV] , 0) AS [NOV],
ISNULL([DEC] , 0) AS [DEC]
FROM (SELECT
--take the 3 first characters of the month name for grouping
LEFT(UPPER(DATENAME(MONTH, TerminationDate)),3) AS [TerminationDate]
FROM
@Employees
--where filter for data between BOM and EOM
WHERE HireDate between DateAdd(month, DateDiff(month, 0, @begin), 0) and dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@end)+1, 0))
) AS dataToPivotTable
PIVOT
(COUNT(TerminationDate) -- value to aggregate
FOR TerminationDate -- what are your horizontal columns (fields)
IN ( [JAN]
,[FEB]
,[MAR]
,[APR]
,[MAY]
,[JUN]
,[JUL]
,[AUG]
,[SEP]
,[OCT]
,[NOV]
,[DEC]
)
) AS whatToPivotOnTable
--================================================
--End TerminationDate pivot
--================================================
--================================================
--Start Insert Summation By Month
--================================================
INSERT INTO @RESULTS
SELECT '# Employees',
SUM([JAN]) AS [JAN],
SUM([FEB]) AS [FEB],
SUM([MAR]) AS [MAR],
SUM([APR]) AS [APR],
SUM([MAY]) AS [MAY],
SUM([JUN]) AS [JUN],
SUM([JUL]) AS [JUL],
SUM([AUG]) AS [AUG],
SUM([SEP]) AS [SEP],
SUM([OCT]) AS [OCT],
SUM([NOV]) AS [NOV],
SUM([DEC]) AS [DEC]
FROM @RESULTS
--================================================
--End Insert Summation By Month
--================================================
--================================================
--View results
--================================================
DECLARE @counter int
DECLARE @NbrMonths int
DECLARE @sql nvarchar(max)
--create temp table because dynamic sql cannot reference a table variable
--previously defined
SELECT * INTO #temp
FROM @results
SET @counter = 1
--get the number of months to display
SET @NbrMonths = datediff(mm,@begin, @end)
--begin the creation of the select statement to dynamically grab the needed columns
SET @sql = N'SELECT Type, [' + LEFT(UPPER(DATENAME(MONTH, @begin)),3) + ']'
WHILE @counter <= @NbrMonths
BEGIN
SET @sql = @sql + N', [' + LEFT(UPPER(DATENAME(MONTH, dateadd(m,@counter,@begin))),3) + ']'
SET @counter = @counter + 1
END
EXEC (@sql + ' From #temp')
DROP TABLE #temp
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply