Pivot distinct values only

  • 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.

  • Not possible?

  • 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;

  • 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.

  • 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

  • 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!

  • 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;

  • 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