LAG, which compares rows within the window partition, is not strictly necessary imo.  JOIN relates to a lookup table containing the values sequence.  Also, I'm not a fan of...

ORs can be quite expensive, so I wrote up this version that uses a different method.  This would probably perform better if you had a separate table that defined the...

ID isn't necessary.  The combination of CommunityID and PersonID should be unique, because a person cannot belong to a community multiple times.  Since you already have a unique key, I...

First, the VALUES clause can take more than one set of values, so you can take advantage of that to make your script MUCH more concise.

insert into #T(VisitID,...

Thanks for the critical feedback! I appreciate your point, but the intent of my script wasn't to compare performance between the two methods. It was focused on providing a...

No, I caught that.  You stated that there is...

I've almost completely abandoned this approach since the introduction of STRING_AGG(), because it's just so much simpler to implement (and therefore easier to understand).  You need to offer a compelling...

From the T-SQL Documentation: Slash Star (Block Comment) (Transact-SQL)

"Nested comments are supported. If the /* character pattern occurs anywhere within an existing comment, it is treated as the start...

It looks like there may also be an error in your logic.  ANDs are evaluated before ORs, and, based on your layout, that's not how you want the WHERE clause...

• This reply was modified 10 months, 3 weeks ago by drew.allen.
• This reply was modified 10 months, 3 weeks ago by drew.allen. Reason: Edited to put in missing end quotes

Not sure why you failed to post sample data and expected results, but you did...

An IF...ELSE is a Control-of-Flow element.  It cannot be used inside of atomic statements, because that would make them non-atomic.  I believe that WHERE clauses can only appear in atomic...

You state that, "Often, we encounter situations where we need to loop through a dataset to process or update records iteratively."  However, the sample that you use can trivially be...

This is where it would help to have sample data.  There are 9 different overlapping relationships, and each usage can have multiple pieces with various instances of those 9 relationships. ...

Did you read the response I made to your other thread?  https://www.sqlservercentral.com/forums/topic/which-is-the-active-price-for-each-ingredient#post-4465061

Drew

Please use the {;}Code button when posting code.