You have two queries here and both have "AND art.[Status] = 'Published'" hard coded into the WHERE clause. AND... That is the same status being referenced in both SELECT lists.

Being that the last day of every year is 12/31, why not just use DATEFROMPARTS...

DECLARE @Year INT = 2017;
SELECT LastDayOfYear = DATEFROMPARTS(@Year, 12, 31);


Is this what you're looking for?

SELECT
    t.EmployeeID,
    t.HireDate,
    t.TermDate,
    NewOrExisting = CASE
                        WHEN t.HireDate = LAG(t.TermDate, 1) OVER (PARTITION BY t.EmployeeID ORDER BY t.HireDate)...

Something like this??? (sorry using a jpg... the forum formatting was killing the SSMS formatting)

If it was working before, I have to assume that all references to the temp table were also within the dynamic sql.

DECLARE @sql VARCHAR(8000) = '

Here's another option that will work in the number of numeric characters can vary...

-- Test Data --
IF OBJECT_ID('tempdb..#TEMP', 'U') IS NOT NULL
DROP TABLE...

If the data in question ALWAYS has the numeric characters in the last 3 positions, there is no need to get user defined functions dragged into the picture...
It can...

Something like the following perhaps?

USE tempdb;
GO

-- Create and populate a 1.5M row TestTable...
IF OBJECT_ID('tempdb.dbo.TestData', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.TestData...

As others have noted in previous comments, there are positives and negatives to all of this. My personal view is that the IoT is coming whether we like it or...

Apparently there are far too few people who know how to do green bar in an SSRS tablix...
So here's how...

1) Go to the Report Properties dialog box and select the...