<removed, dup comment>

@jonathan-2:

This method of determining Tuesday:

DATEPART(weekday, @CurrentDate) = 3

requires a specific DATEFIRST setting to work correctly.  Many business have locations around the world, so it's safer to use a method that...

@dennis Jensen:

I never presume to know the requirements of the task better than the person requesting the code.

"If the first Monday of the month is a holiday, than I need...

I'd strongly urge you not to mess with the DATEFIRST setting.  You don't need to, and it could throw off other code which might need a different setting (if the...

I don't believe you can do this directly.  However, you could have the first step of the job determine if it is a holiday, and if so, to schedule a...

Your ordering is not guaranteed to be in the OUT ITEM order, since you don't sort by it.

If you have a lot of rows, you'd likely get better performance putting the results (from the cte) into an indexed temp table, but for now, just see if this...

An alternative that allows a previous open paren(s) in the input data:

DROP TABLE IF EXISTS #test2;
CREATE TABLE #test2 ( tran_log_writes nvarchar(4000) NULL );
INSERT #test2 ( tran_log_writes )...

Personally for something this involved / complex, I'd use a custom function that parsed with pre-determination.  That is, the code doesn't look only for specific names (tags) in the data,...

You specify either a partition or a filegroup, not both (if partition, the partitioning will determine the filegroup(s) used).

create index IX_Service_Start_Date ON ivc_cds.CDS_Claim_Header (Service_Start_Date )

WITH (FILLFACTOR = 98, SORT_IN_TEMPDB =...

Try this:

SELECT DISTINCT Lotr.LabOrderUnique,
    STUFF( (SELECT DISTINCT CAST('^' AS varchar(max)) + CAST(lotr1.TestName AS varchar(max))
        FROM dbo.Ncs_conv_IntergyMM_Labordertestresult AS...

You very likely already have a clustered index on the table, on ( ClaimHeaderID, ClaimLineID ).  (That's a vastly better clus key than an identity column, btw.)

You can only have one...

But you're going to have to include the date in the clustering key to do partitioning anyway, you might as well change the clustering first.  And that will be less...

By far the best performance payoff for this is creating the best clustered index on the table, as coded below.  You would likely not even need to partition then.

Based on...

Clustering by the date will solve the performance issues.  Partitioning will aid performance only if you need to rebuild the table, in which case you would only need to rebuild...