January 22, 2018 at 2:52 pm
i need to report on the last six - last day of the month counts by department. The query below ...
SELECT EOMONTH(GETDATE(), -6) AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -6) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT EOMONTH(GETDATE(), -5) AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -5) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT EOMONTH(GETDATE(), -4) AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -4) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT EOMONTH(GETDATE(), -3) AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -3) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT EOMONTH(GETDATE(), -2) AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -2) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT EOMONTH(GETDATE(), -1) AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -1) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
generates data that looks like this (only the last month complete results shown)
AsOf Department Headcount
2017-12-31 Appraisal Desk 12
2017-12-31 Closing 27
2017-12-31 Compliance 14
2017-12-31 Consultant 3
2017-12-31 Consumer Direct 30
2017-12-31 Disclosing 17
2017-12-31 Encompass Support 6
2017-12-31 Executive 5
2017-12-31 Executive Assistant 1
2017-12-31 Finance-Accounting 10
2017-12-31 Finance-Funding 5
2017-12-31 Finance-Other 5
2017-12-31 Human Resource 3
2017-12-31 IT 9
2017-12-31 IT Help Desk 6
2017-12-31 Marketing 7
2017-12-31 OPS 17
2017-12-31 Post Closing 25
2017-12-31 Processing 131
2017-12-31 Sales 299
2017-12-31 Sales Support 100
2017-12-31 Secondary 8
2017-12-31 Servicing 5
2017-12-31 Staff 21
2017-12-31 Underwriting 60
2017-11-30 Appraisal Desk 11
2017-11-30 Closing 28
2017-11-30 Compliance 15
and so on ...
Is it possible to pivot this so that the columns are the distinct last-of the-month dates, the rows are the departments and the headcounts are the values? I have not been able to get the distinct dates as columns!
Thank you.
January 24, 2018 at 11:36 am
Not possible?
January 24, 2018 at 12:05 pm
I was able to accomplish this mostly. I could not do it with the actual end of month dates but with the number of months ago.
SELECT
*
FROM
(
SELECT 6 AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -6) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT 5 AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -5) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT 4 AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -4) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT 3 AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -3) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT 2 AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -2) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
UNION
SELECT 1 AsOf, Department, COUNT(*) Headcount FROM Dim_Census WHERE EOMONTH(GETDATE(), -1) BETWEEN ProfileStartDate AND ISNULL(ProfileEndDate, '1/1/2099') GROUP BY Department
) src
PIVOT
(
SUM(Headcount) FOR AsOf IN ([1],[2],[3],[4],[5],[6])
) piv;
January 24, 2018 at 12:33 pm
Maybe some combination of determining the month dates first, then using the "cross tab" method as described here:
http://www.sqlservercentral.com/articles/T-SQL/63681/
I've found the cross tab method to be more flexible than using PIVOT operator, and can perform better. The key here though would be what you expect for the column names after pivoting. If you want the month end dates to be the column names, that might require some dynamic SQL.
January 24, 2018 at 2:45 pm
If you want the dates as the header, you will need to either use dynamic SQL or use a formula in your presentation layer.
There are also issues with your CTE. You have essentially the same query SIX times and the criteria are not SARGable. I've tried to rewrite it so that the query is only run once with SARGable criteria.
WITH Cal AS
(
SELECT n, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - n, -1) eom
FROM ( VALUES(1), (2), (3), (4), (5), (6) ) Tally(n)
)
SELECT n AsOf, Department, COUNT(*) Headcount
FROM Dim_Census
INNER JOIN Cal
ON eom >= ProfileStartDate
AND ( eom <= ProfileEndDate OR ProfileEndDate IS NULL )
GROUP BY Department, n, eom
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 25, 2018 at 7:18 am
Thanks Drew and Chris! I did handle the column headers in the presentation so they are the actual dates. I was unfamiliar with "Tally". Very cool!
January 25, 2018 at 7:27 am
Using Drew's query and pivot gets me what i want. Thanks again.
WITH Cal AS
(
SELECT n, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - n, -1) eom
FROM ( VALUES(1), (2), (3), (4), (5), (6) ) Tally(n)
)
SELECT *
FROM
(
SELECT n AsOf, Department, COUNT(*) Headcount FROM
Dim_Census
JOIN Cal
ON eom >= ProfileStartDate
AND ( eom <= ProfileEndDate OR ProfileEndDate IS NULL )
GROUP BY Department, n, eom
) src
PIVOT
(
SUM(Headcount) FOR AsOf IN ([6],[5],[4],[3],[2],[1])
) piv;
January 25, 2018 at 8:51 am
There are a number of good articles here on SQL Server Central (and elsewhere too) about Tally tables, (sometimes called numbers table), their advantages, and how to use them. I've found having a tally table to be a great utility in a number of situations. You can also simulate one as Drew did in his query.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply