I made a couple of assumptions that may not be accurate, but the following seems to work.  The detail sections have six records where the first character ranges from 1-6;...

Here is another approach.  My initial testing indicates that it should be faster.

WITH CTE AS
(
    SELECT *, MIN(TestResult) OVER(PARTITION BY MemberID ORDER BY CycleID...

Here is an approach that requires one fewer sorts than Chris M.'s method.

It uses a fairly standard technique for packing intervals developed by Itzik Ben Gan.

Your...

It's because you're using the wrong PARTITION BY clause.  It's not clear what the correct one is, but you should NOT include Country in the partition.  Also, one of your...

It's asking you to generate two result sets, and you're only generating one.  In trying to combine the two into one, you've added unnecessary complexity to the query.

Drew

It's probably easier to just process them in batches rather than run through them to add batch numbers and then run through them again to process them.  Here is an...

There are two main approaches.  Which one performs better depends on the density of table 2 with respect to table 1 and to a lesser extent on how many records...

There are actually a couple more options, but the one that you were likely looking for is called a correlated subquery.  The subquery can reference values from the outer query,...