Partitioning and Ordering in Multiple Union Statements

Question

Good morning,

I am working on combining many metrics for our dashboarding team into a single executable stored procedure they could copy into Excel and then let pivot tables do some magic. That being said, for the sake of this question I will only be referencing a single smaller dataset for a single metric to keep it simple. The way they want headers and labels is very specific so that is why the first couple of sets are written the way they are. I can give more info if needed.

Here's the issues I am wondering if someone could help me with:

1. They want the discharge month name in the query. That's fine, but once I add the discharge date (projected_d) into the query I have to also use a group by. This is then causing any Month or Measure with no data to completely disappear from the result set. I would like to show a zero for that line as opposed to disappearing. If I remove the month and group by from the query the zeroes will show.

2. I cannot use an ORDER BY statement for the Months due to the Union All. I want to display the month name in the result set but order it by MONTH number. I generally don't have issues doing that in normal queries, but due to the Union Alls in this query I am having issues on where or how to make that work.

Here's the current query and the result set:

USE COMPANY_VHR_PROD
GO

DECLARE @MINDOS DATE,
        @MAXDOS DATE,
        @PRTFSuccess INT,
        @PRTFTotal INT

SET @MINDOS = '20170701'
SET @MAXDOS = '20170803'

SELECT @PRTFSuccess = COUNT(DISTINCT uniqueid_c)
FROM cd.enrollments
WHERE
    program_c = 'PRTF'
    AND level_c IN ('SIG', 'MOD')
    AND projected_d BETWEEN @MINDOS AND @MAXDOS

SELECT @PRTFTotal = COUNT(DISTINCT uniqueid_c)
FROM cd.enrollments
WHERE
    program_c = 'PRTF'
    AND projected_d BETWEEN @MINDOS AND @MAXDOS

SELECT
    '' AS "Program",
    '' AS "Measurement",
    '' AS "Month",
    '' AS "Count or Total",
    '' AS "Percentage or Average",
    '' AS "YTD"
WHERE 1=0

UNION ALL

SELECT
    '',
    '',
    '',
    '',
    '',
    ''

UNION ALL

SELECT
    'PRTF',
    'Client Response To Treatment',
    'Month',
    'Count',
    'Percentage',
    'YTD'

UNION ALL

SELECT
    'PRTF' AS "Program",
    CASE 
        WHEN e.level_c IS NULL THEN 'None Entered' 
        WHEN e.level_c = '' THEN 'None Entered' 
        WHEN e.level_c = 'SIG' THEN 'Significant Success Experienced'
        WHEN e.level_c = 'MOD' THEN 'Moderate Success Experienced'
        WHEN e.level_c = 'MILD' THEN 'Mild Success Experienced'
        WHEN e.level_c = 'INS' THEN 'Insignificant Success Experienced' 
        ELSE e.level_c END AS "Success",
    DATENAME(MONTH,e.projected_d),
    CONVERT(VARCHAR,COUNT(DISTINCT e.uniqueid_c),1) AS "Count",
    CONVERT(VARCHAR,CAST((COUNT(DISTINCT e.uniqueid_c) / CONVERT(DECIMAL(10,2),@PRTFTotal))*100 AS DECIMAL(10,2)),1),
    CONVERT(VARCHAR,SUM(COUNT(DISTINCT e.uniqueid_c)) OVER (PARTITION BY e.level_c),1)
FROM cd.enrollments e
WHERE
    e.program_c = 'PRTF'
    AND e.projected_d BETWEEN @MINDOS and @MAXDOS
GROUP BY e.level_c, DATENAME(MONTH,e.projected_d)

UNION ALL

SELECT
    'PRTF' AS "Program",
    'Total Clients Discharging' AS "Client Response to Treatment",
    'All',
    CONVERT(VARCHAR,COUNT(DISTINCT e.uniqueid_c),1) AS "Count",
    CONVERT(VARCHAR,CAST((COUNT(DISTINCT e.uniqueid_c) / CONVERT(DECIMAL(10,2),@PRTFTotal))*100 AS DECIMAL(10,2)),1),
    CONVERT(VARCHAR,COUNT(DISTINCT e.uniqueid_c),1) AS "YTD"
FROM cd.enrollments e
WHERE
    e.program_c = 'PRTF'
    AND e.projected_d BETWEEN @MINDOS and @MAXDOS

ResultSet

Ideally I would want it to show something like this, in this order:

PRTF - Insignificant Success - July   - 0 -   0.00 - 0
PRTF - Insignificant Success - August - 0 -   0.00 - 0
PRTF - Mild Success          - July   - 0 -   0.00 - 0
PRTF - Mild Success          - August - 0 -   0.00 - 0
PRTF - Moderate Success      - July   - 1 -  25.00 - 1
PRTF - Moderate Success      - August - 0 -   0.00 - 1
PRTF - Significant Success   - July   - 2 -  50.00 - 3
PRTF - Significant Success   - August - 1 -  25.00 - 3
PRTF - Total Clients         - All    - 4 - 100.00 - 4

Can anyone help me out?

Answer 1

You can use sub-queries to inject the month number and a sort order to handle sorting of the headers, body, and footer. The sub-queries would still be combined with the UNION ALL, but it allows you to select only relevant columns of result set while giving use metadata for the sorting.

-- Load headers with sub-query to hide sort order attribute
SELECT
    [Program],[Measurement],[Month],[Count or Total],[Percentage or Average],[YTD]
FROM (
    SELECT '', '', '', '', '', '', 10, 0 -- SortOrder, MonthOrder
    UNION ALL SELECT 'PRTF', 'Client Response To Treatment', 'Month', 'Count', 'Percentage', 'YTD', 20, 0 -- SortOrder, MonthOrder
) Headers ([Program],[Measurement],[Month],[Count or Total],[Percentage or Average],[YTD], SortOrder, MonthOrder)

UNION ALL

-- Load records with sub-query to hide sort order attribute
SELECT
    [Program],[Measurement],[Month],[Count or Total],[Percentage or Average],[YTD]
FROM (
    SELECT
        'PRTF' AS "Program",
        CASE 
            WHEN e.level_c IS NULL THEN 'None Entered' 
            WHEN e.level_c = '' THEN 'None Entered' 
            WHEN e.level_c = 'SIG' THEN 'Significant Success Experienced'
            WHEN e.level_c = 'MOD' THEN 'Moderate Success Experienced'
            WHEN e.level_c = 'MILD' THEN 'Mild Success Experienced'
            WHEN e.level_c = 'INS' THEN 'Insignificant Success Experienced' 
            ELSE e.level_c END AS "Success",
        DATENAME(MONTH,e.projected_d),
        CONVERT(VARCHAR,COUNT(DISTINCT e.uniqueid_c),1) AS "Count",
        CONVERT(VARCHAR,CAST((COUNT(DISTINCT e.uniqueid_c) / CONVERT(DECIMAL(10,2),@PRTFTotal))*100 AS DECIMAL(10,2)),1),
        CONVERT(VARCHAR,SUM(COUNT(DISTINCT e.uniqueid_c)) OVER (PARTITION BY e.level_c),1),
        30, DATEPART(MONTH,e.projected_d) -- Add SortOrder, MonthOrder
    FROM cd.enrollments e
    WHERE
        e.program_c = 'PRTF'
        AND e.projected_d BETWEEN @MINDOS and @MAXDOS
    GROUP BY e.level_c, DATENAME(MONTH,e.projected_d)
    ) Records ([Program],[Measurement],[Month],[Count or Total],[Percentage or Average],[YTD], SortOrder, MonthOrder)

UNION ALL

-- Load footer with sub-query to hide sort order attribute
SELECT
    [Program],[Measurement],[Month],[Count or Total],[Percentage or Average],[YTD]
FROM (
    SELECT
        'PRTF' AS "Program",
        'Total Clients Discharging' AS "Client Response to Treatment",
        'All',
        CONVERT(VARCHAR,COUNT(DISTINCT e.uniqueid_c),1) AS "Count",
        CONVERT(VARCHAR,CAST((COUNT(DISTINCT e.uniqueid_c) / CONVERT(DECIMAL(10,2),@PRTFTotal))*100 AS DECIMAL(10,2)),1),
        CONVERT(VARCHAR,COUNT(DISTINCT e.uniqueid_c),1) AS "YTD",
        40, 0 -- Add SortOrder and month order
    FROM cd.enrollments e
    WHERE
        e.program_c = 'PRTF'
        AND e.projected_d BETWEEN @MINDOS and @MAXDOS
    ) Footers ([Program],[Measurement],[Month],[Count or Total],[Percentage or Average],[YTD], SortOrder, MonthOrder)

ORDER BY
    SortOrder, [Program], [Measurement], [MonthOrder]