September 10, 2012 at 8:28 pm
I am trying to show how many employees were hired in each department by startyear (2001, 2002, 2003) and then to rollup these year totals by department. So, for example, Engineering hired one employee in 2001, another in 2002, and another in 2003 with 3 total in the Engineering department.
I am able to get the first part of this problem solved with a PIVOT (showing 1 employee under each year) but I'm unable to add a GROUP BY clause after the PIVOT that will rollup each years total by department to show total number (3) of employees hired by department (eg. Enginneering).
Is GROUP BY the solution or is there another way to roll up by DeptName?
---this works:
select DeptName, [2001], [2002], [2003]
from
(select DeptName, EmpID, year(StartDate) as startyear
from StaffHistory
) as p
PIVOT (count(empid) for Startyear in ([2001], [2002], [2003])) as pvt
---this doesn't
select DeptName, [2001], [2002], [2003]
from
(select DeptName, EmpID, year(StartDate) as startyear
from StaffHistory
) as p
PIVOT (count(empid) for Startyear in ([2001], [2002], [2003])) as pvt
GROUP BY DeptName
CREATE TABLE StaffHistory
(DeptName varchar(40)NOT NULL, EmpID int NOT NULL, StartDate datetime NOT NULL, EndDate datetime NULL);
INSERT INTO StaffHistory
VALUES
('Engineering', 1, '2003-12-16 00:00:00.000', null),
('Tool Design', 2, '2003-07-01 00:00:00.000', null),
('Sales', 3, '2003-11-01 00:00:00.000', null),
('Engineering', 4, '2002-07-01 00:00:00.000', null),
('Tool Design', 5, '2002-07-01 00:00:00.000', null),
('Sales', 6, '2002-11-01 00:00:00.000', null),
('Engineering', 7, '2001-07-01 00:00:00.000', null),
('Tool Design', 8, '2001-07-01 00:00:00.000', null),
('Sales', 9, '2001-11-01 00:00:00.000', null);
--Quote me
September 10, 2012 at 9:01 pm
Try this, it will give you a start.
SELECT DeptName
, [2001]
, [2002]
, [2003]
, [All]
FROM (
SELECT DeptName
, startyear = isnull(cast(year(StartDate) AS CHAR(4)), 'All')
, Cnt = Count(empid)
FROM StaffHistory
GROUP BY DeptName
, year(StartDate)
WITH rollup
) src
pivot(sum(Cnt) FOR startyear IN (
[2001]
, [2002]
, [2003]
, [All]
)) pvt
WHERE DeptName IS NOT NULL
Sam Vanga
http://SamuelVanga.com
September 10, 2012 at 9:36 pm
This solution works, Thanks Sam.
I have a question about this line:
, startyear = isnull(cast(year(StartDate) AS CHAR(4)), 'All')
I understand ISNULL works because a NULL for startdate is created by the creation of an All column. But, can you explain why it is that you have to use the CAST function, as in , "cast(year(StartDate) AS CHAR(4)"? Is it because datetime is not convertible to a string value?
--Quote me
September 11, 2012 at 8:32 am
Look at the remarks section here: http://msdn.microsoft.com/en-us/library/ms184325.aspx
When using ISNULL, check and replacement expressions should be of compatible data types.
Sam Vanga
http://SamuelVanga.com
September 11, 2012 at 11:57 am
For general knowledge and maybe performance (you'll have to test your case), I leave you with my personal preference:
SELECT DeptName,
SUM( CASE WHEN YEAR( StartDate) = 2001 THEN 1 ELSE 0 END) [2001],
SUM( CASE WHEN YEAR( StartDate) = 2002 THEN 1 ELSE 0 END) [2002],
SUM( CASE WHEN YEAR( StartDate) = 2003 THEN 1 ELSE 0 END) [2003],
COUNT(*) [All]
FROM StaffHistory
GROUP BY DeptName
You can find more information here:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns [/url]
May 2, 2018 at 1:51 am
OPTION(RECOMPILE) is fix this problem
Article;
https://dervisali.wordpress.com/2011/07/13/sql-lokal-degiskenler-ve-sorgu-performansi-option-recompile/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply