I am going to take a little different approach - the CTE should define the detail data and then we can summarize that detail data.  There really isn't any need...

• This reply was modified 2 years ago by Jeffrey Williams.

Here are some recommendations:

1. Remove the explicit transaction - it isn't needed because you have a single insert being performed and that will - by default - be an implicit...

Thanks Jeffery

Yeah we contacted the vendor who said they feel the issue is permission related.

The error is not occurring in that code - or it is happening somewhere later...

SELECT aging_1_30  = SUM(CASE WHEN st.HowManyDays <= 30 THEN st.thisAmt END)
     , aging_31_60 = SUM(CASE WHEN st.HowManyDays > 30 AND st.HowManyDays <=...

Thanks Jeffrey. Other thing I was looking for indexing and also to remove the UPPER() function. This database is not case-sentive database. I am not sure, why they are...

If the dev is stating they only want the latest 30 minutes of data - then the code is wrong.  That code is pulling all of the data except the...

You need to put a ticket into Optum/Ingenix - most likely you have a version issue between the database and application.

With that said, the error you are getting references an...

Also, if you really are using RCSI, then using (NOLOCK) is a complete waste of code realestate.  Also, using (NOLOCK) by itself was deprecated years ago.  MS says the...

A bit confusing - you state it runs quickly in 5-10 minutes (not quick in my opinion) - then later on say it takes 4 seconds.  Either way, the first...

By copying all rows and all columns into a temp table - you remove any possibility of utilizing indexes on the source table.  So every time you query that temp...

I just remember the ones I use the most - and CHAR(39) is one of those.  For all others: https://www.asciitable.com/

I prefer using CHAR instead of multiple single-quotes:

SELECT STRING_AGG(QUOTENAME(td.[name], CHAR(39)), ',')
FROM #tbl_db AS td;

Why are you selecting everything into a temp table?  You should filter that down to just the columns you need - and only the rows that are needed.

For a running total - you can use: thisBalance = SUM(thisAmt) OVER(PARTITION BY Batch_Id ORDER BY Id)

If you want to break out the totals even further, add those to the...

There are a couple of options:

1. Add a new file to the filegroup sized at 1TB with a large auto growth setting (20GB or more).  Issue a DBCC SHRINKFILE on...