Changing your "UNION ALL"s to "UNION"s so you remove duplicates in the recursive lookup may help.

I also agree with Scott here that you should update your joins to be in...

your available physical memory is pretty low, and I am pretty sure that SQL won't run on Virtual memory.  My guess here (without seeing your system) is you are out...

I see you have quite a large chunk of code there and from a quick eyeballing of it, is it duplicated code?  Do you really need to go through those...

I am glad to help.  I am glad that fixed the issue too (hopefully).

Sorry the code isn't more efficient.  I'm sure there are others on the forum who could look...

Think I see my typo... try changing:

BEGIN
IF NOT EXISTS
(
SELECT
1
 FROM@result
 WHERE[tripID] = @tripID2
)
BEGIN
SELECT
@correctedLoop = [grouping]
 FROM@result
 WHERE[tripID] = @tripID2;
END
ELSE
BEGIN
    
SELECT @correctedLoop = [grouping]
FROM @result
WHERE...

• This reply was modified 5 years, 8 months ago by Mr. Brian Gale.

As a weird thought, are you sure they are duplicates?  You said that it wasn't until you opened up a wider date range... I am wondering if MAYBE it is...

I have a thought on that... I think I missed one of the "sanity" checks in the CURSOR... try this:

;WITH [cte]
AS
(
SELECTDISTINCT
[t].[rn]  AS [rn]
, [t].[ldate]  AS...

From the Microsoft documentation to enable query store on SQL 2016:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE =...

I think the issue is Scott's query has a typo:

INNER JOIN #Duplicate D ON D.ID = D_First.ID AND D.Market = D_First.PL AND

should be:

INNER JOIN...

How does this look:

WITH cte AS (
SELECT [id]
 , [Father_id]
 , [Flag_Root]
 , root = [id]
, 0 AS N
FROM @tab
WHERE [Flag_Root] = 1
UNION ALL
SELECT [t].[id], [t].[father_id], [t].flag_root,...

I am glad I could help!  I found the problem to be interesting and fun to work on.

Been a while since I had a fun, challenging SQL query like that...

This sounds like a homework assignment...

Question 1 is pretty trivial as you are just using datediff on the Cases table with a WHERE to put the date greater than or...

I didn't even notice it dropped trip id 28754... Ok, did ore refactoring and now I get 19 results with most being overlaps of 2 except the last one which...

I think it is close, but thinking we are going about this from the wrong approach.

How about something like this:

-- Get initial data
WITH [cte]
AS
(
SELECTDISTINCT
[t].[rn]  AS [rn]
,...

So, if I understand correctly, as soon as there is an overlap, it is grouped by that.  So, if you have for example the following:

INSERT #TestTrips ([rn],...