The only “downside” is that the entire report and the underlying queries get executed with every refresh.

There are two things I’d change.

1) Use a permanent table, not a temp table....

removed dupe

• This reply was modified 4 months ago by  Jason A. Long.

removed dupe

Did you ever find the solution? If so, would you be kind enough to...

For the love of whatever you hold dear, stop using “MMDDYYYY” formatting!

It doesn't sort chronology as text. You should be using “YYYYMMDD”.

I don't see where anyone has already said it, so I'll say it… “MMYYYY” is a date format to avoid. Using “YYYYMM” instead will allow you to actually sort those...

Try pulling a space between SELECT and T6...

That’s the thing about tempdb, it’s true to it’s name. Nothing you put there is truly permanent. By using normal create commands in tempdb you don’t have to worry about...

Just create a “permanent” table in tempdb. It will persist and be available like any other table, until it is explicitly dropped or or the SQL service is restarted.

Maybe I'm missing something but it seems that there are two easier options...

1) put everything on your local test machine into SIMPLE recovery.

2) if #1 isn't an option, just do...

Wow.  Thanks Jason.  I seriously had no clue.  Like I said, my aim is to help, not collect accolades for a resume but that comment just made the last...

Thanks, Jason.  I have to tell you, you've made my month.  I had no idea about that and I sometimes wonder to myself whether or not I accomplished my...

Simply giving credit where credit is due. Prior to this particular article, the benefits of nested sets were known...

Now that is impressive! Thank you for the update Jonathan.

Assuming the pattern in the sample data is actually representative of your production data, the following would be simpler and more efficient as it eliminates the need to unpivot, sort...

• This reply was modified 1 year, 8 months ago by  Jason A. Long.