Ha!  Yeah, that was going to be my last resort.  Since I recently inherited this server, I...

I thought that was accepted "Best...

There is a serious problem with the "equivalent" code you wrote though.  It's not "equivalent" in the results for the last 3 tests.  The table code comes up...

@ Jonathan,

I modified your code and comments a bit to provide an easier to read output on SQL Profiler.  I also made each snippet write to a throwaway variable to...

My belief is that you're doing this whole thing the wrong way.  Have a look at how easy it can be at the following URL.

https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

Depending on your reporting requirements,...

I figured it might be better than my BSOFH method which is to take the database offline and wait for the email. 😀

I've always considered the following article to be a must read on this subject.

https://www.itprotoday.com/sql-server/new-solution-packing-intervals-problem

The way we do it is that we don't check when was the last transaction, we put an extended event capturing anything that goes into the database and we...

Scott was the first to respond. 😉

Thank you very much Jeff. I noticed one thing if there is none changed in the latest month it is not showing any record for the month it would...

Just remember... If you...

That was just one of the problems with the data v.s. what you wanted for the output.

Anyway, here's the code you need.

WITH cte AS
(
 SELECT  FileMonth...

The latest version of the code works just fine now.  I also took a look at your expected output.  We don't need to create any code to know that your...

You really need to actually try to run the code you posted before you post it.  Heh... you even apparently ignored what I told you what was wrong with the...

Ok. That sounds like the only reason you're adding the GUID is to try to avoid fragmentation.  By itself, that's probably not a good enough reason.  And, again, there's no...