What is the text of the error you are receiving?

Is the data exactly the same across the different environments?

You need to find out more from the person asking you to do this. Questions such as

What sort of validation? Data within certain limits? Data has only predefined values? Data...

Wow, don't be offended, but what a mess! It might be time to think about a divide and conquer approach. Build up your result sets individually and then assemble them...

• This reply was modified 1 year, 10 months ago by Phil Parkin.

We can't even properly see your query! How is anyone supposed to know what code is in that function?

After...

• This reply was modified 1 year, 10 months ago by Phil Parkin.

What is the datatype of SERVICE_CD? CASTing every element to VARCHAR(MAX) sounds like madness.

Your DISTINCT is applied to the result of the STRING_AGG, not to the elements it contains.

Remove the dupes in a CTE and select your STRING_AGG from that.

Thank you all for the help!  Phil's suggestion worked for me, I was trying to avoid it as there is no leading index on the date columns.

I as well...

I don't see why there should be a cast-iron association between tableAID and LastUpdateDateTime, assuming that any row, old or new, can be updated (and thus have its LastUpdateDateTime modified).

Instead,...

While this is an interesting technique, I'd suggest that the C# script could easily be modified to do the actual copy/move, obviating the need for a Foreach loop. This would,...

Something like this, perhaps.

PrevSickBalance = LAG(Accrue_Sick_Balance,1,0) OVER (Partition By Employee_ID ORDER BY Payroll_Detail_ID)

• This reply was modified 1 year, 11 months ago by Phil Parkin.

How about providing your sample data in consumable format, along with your desired results? You've been here long enough to know better

That's a huge chunk of code and, in my opinion, asking such general questions is the sort of thing you'd employ a paid consultant to help with.

If you take the...

In the one that doesn't work, I suspect it's because the TOP (1) is being applied before the join on StaffId.

If you were to push the StaffId into the subqueries...

• This reply was modified 1 year, 11 months ago by Phil Parkin.

Then you will need to...