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 5 months, 4 weeks ago by  drew.allen.
• This reply was modified 5 months, 4 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.

My primary goal is to meet the stated business requirements for the code, which I don't believe your code does.  You will list the "truePct" for even a single...

Maybe this?:

SELECT  poll_id ,start_date,end_date,
candidate_name, sample_size,
(select avg(pp2.pct) from [president_polls] pp2 
    where pp2.state = pp.state and pp2.poll_id = pp.poll_id 
   ...

Here is the beginning of a solution.  It uses James F. Allen's (no relation) Interval Algebra:  https://en.wikipedia.org/wiki/Allen%27s_interval_algebra.

The amount used from each bin depends on the relation between the purchases...

• This reply was modified 6 months, 2 weeks ago by  drew.allen.