You could use "location" still, but put []'s around it too.  My habit is to put [] around my objects.  Never know when a new version of SQL...

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

I think my only question here is what do  you consider a "valid" revised start/end dates?  for example, if you had this:

start              end

Dec 1,2020...

If I understand that script correctly, you are essentially doing a find and replace with a large list of characters to swap out, correct?  If the character is "aù", you...

You could use "location" still, but put []'s around it too.  My habit is to put [] around my objects.  Never know when a new version of SQL will add...

I don't think telling SQL it can use more memory is the way to go when you are possibly having memory pressure already. If I understood you correctly you said...

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, 10 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,...