You haven't provided nearly enough information to even begin to answer this.

Drew

And what happens when someone has a first name that includes a space?  "Jackson, La Toya"

Drew

I looked at this, and it looks like your data is too clean to be representative, because your...

I would use the Table Value Constructor version instead of the standard UNPIVOT, because it's more flexible and you need that flexibility here.  I've used a sample where the StartDate...

What severity are you using in your RAISERROR?

It sounds like you don't have your BEGIN/END statements placed properly.  You should never be able to process both sides of...

It looks like a fairly standard UNPIVOT.  What are you having difficulties with?

The main issue that I see is that you are storing your dates in a non-standard...

So you're okay with someone who has a gap that crosses years?

INSERT INTO #t(BeneficiaryID, EligYear, EligMonth )
Select '12345678',2016,6 UNION
Select '12345678',2016,7 ...

Why are you testing for both IsWeekend = 1 and weekdayno = 7?  If weekdayno = 7, then...

This gives the expected results and only requires one scan of the table.

SELECT col1, CASE WHEN GROUPING(col2) = 1 THEN 'diff' ELSE col2 END AS col2,...