Doh, well I'd like to edit my edit in the previous post.  Instead of "Removed IN from WHERE clause" it should say "Removed IN from WHEN condition" or something like...

Maybe add a boolean column to the partition

select *, v.grp,
       [test33] = case when v.grp=1 
     ...

• This reply was modified 3 years, 3 months ago by Steve Collins. Reason: Removed IN from WHERE clause

Thanks to ChatGPT for creating the temp table

DROP TABLE if exists #TempTable;
go
CREATE TABLE #TempTable
(
    ID INT,
    flag int,
   ...

For the first query ChatGPT-4 generated

WITH CompanyAverageRent AS (
    SELECT c.CompanyId, AVG(p.Rent) AS AverageRent
    FROM #Companies c
    JOIN...

What happens if/when there are gaps in the CreatedDate column for particular CustomerID's?

additional JOIN's and CROSS APPLY's apply to the rectangle defined immediately above it (in order of appearance) within the FROM clause

Thinking inside rectangles is how I...

The link Steve Collins posted is interesting. Got through it, and while I don't quite agree that stored procs create a strangler pattern, I do think in the high...

Check out this video.  The speaker gives a whole laundry list of reasons, a litany of epiphanies, on why SQL Server is not well suited for transaction processing.  Instead, the...

It seems you could use COUNT in the SELECT list.  COUNT always returns an INT even if the FROM clause returns no rows

declare
  @max_num  ...

This is a tricky query.  You have 2 separate aggregations for which the results are then LEFT JOIN'ed by rank.  What I'm not understanding in my own code is why...

Does the query contain WITH (NOLOCK)?

Please run this code and paste the results.  Please replace 'Your-Database-Name'...

An alternative

drop table if exists #title_one_space;
go
select * into #title_one_space from (values
 ('one word')
,('one word two words')
,('one word two words three words')
,('one word two words three words four'))...

It shouldn't be necessary to calculate the LAG twice.  Like Drew's code this factors out another CTE

  WITH 
 ctePrev AS 
(--==== Get the previous Prices...