Then use LAG() and maybe ROW_NUMBER().
Any record with a non-null LAG() value will be a reopen.

How far between open/close would count as a "reopen"? You can just use LAG() with a PARTITION to look backwards at the previous records.

So when you created small, medium-sized, large, and ridiculously large TVPs and JSON files and queried them, what did the results look like?

Why not set the collation to case-insensitive instead of doing all those converting to upper case? But without some sample data, and a sample of query parameter values, it's hard...

"Consumable data" means CREATE TABLE and INSERT scripts that people here can copy & paste into SSMS to recreate your problem. If you don't do that, it's very likely you...

Looks like this is the answer:
https://stackoverflow.com/questions/12676747/how-to-get-the-affected-rows-in-vba-ado-execute

Luis,
I don't think that will work. That's why I did the weird REPLACE.
The Prod | 5 and Prod | 1 records split to separate records when they should...

Oh... maybe I figured out what Jonathan was talking about...
SELECT id, [status], p2, REPLACE(ca.Item, '_',' ') AS NewItem
FROM
    (SELECT id, [status], REPLACE(Products,' ','_') p2
    FROM MyData

Yeah... I was having a fit trying to get the formatting etc to work. Does this site not like Edge? (Maybe I should just break down and use Chrome instead?)

Yeah, like that!

Your sample data is messed up. I removed a bunch of columns so that I had a minimum to deal with. In a word, you need DelimitedSplit8K to do this...

I think you'd have to use LAG() to look at the previous visit for a given patient. Then use DATEDIFF() to get the days between the two dates, and if...

See https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

See Example D.
USE AdventureWorks2012;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel)
AS (   SELECT ManagerID,
       ...

script out all the constraints,
drop them all,
import data,
re-add the constraints by running script from step 1

If you use RIGHT([oldSSN],4) then the rest shouldn't matter, because you'll never get any of the dashes anyway, because they would be 5th from the right.