Franklly, all the effort to play the games with PrepareStatement just doesn't make much sense to me, when you could just initiate a connection and execute a stored procedure.   The...

Here's an idea query that you should be able to adapt: IF OBJECT_ID(N'tempdb..#t') IS NOT NULL
    BEGIN
    DROP TABLE #t;
    END;
GO

CREATE TABLE #t (
    BeneficiaryID varchar(10),
    EligYear...

Sounds like a "gaps and islands" type of problem.   However, I think you're going to find a problem with your very first CTE.   If you take a MIN value for...

Need a LOT more detail...   What type is your parameter?   What data type does your query or stored procedure expect?   Does the query/stored procedure filter data based on that parameter? ...