Would something like this work for you?

WITH
    cte_PostalVariations AS (
        SELECT DISTINCT    -- only use DISTINCT if there is a possibility of dupes in the source...

Yup... I read it as, "I haven't been out of England in 35 years"...
In that case, I'm back to my original suggestion... Make sure she doesn't skimp on the...

Eh... What the heck...

CREATE FUNCTION dbo.tfn_FederalHoliday
/* =====================================================================================================
05/02/2017 JL, Created. If the supplied is a federal holiday, the function will return the holiday name.
=====================================================================================================...

I tried to throw a few bucks to the cause but got the following message...

For a case like this, nothing beats a good ol' fashioned calendar table... At least in my humble opinion...
Fact is, holidays tend not to change very often (if ever)...

Give this a try...

WITH
    cte_AddRN AS (
        SELECT
            t.admdt,
            t.dischdt,
            t.ID,
            RN = ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.admdt)
        FROM
            #t t
        WHERE...

Working with the page headers & footers is, as you've noticed is significantly trickier... and... what you're attempting is a bit unorthodox.
Assuming that you're dealing with a relatively small...

Here's an example from one of our reports that successfully drills through to a new report in a new window...

="void(window.open('" & Globals!ReportServerUrl & "?" & Globals!ReportFolder &...

Here's a slightly different option that concatenates the the column name in a variable rather than using the FOR XML method

DECLARE @UnpivotList NVARCHAR(MAX) = N'';
SELECT...