#5 is completely wrong approach.  You should never use ISNULL() in a WHERE clause.  Instead move the condition from the WHERE clause to the LEFT JOIN.  You need to that...

Can't just take your word for that, still need to see DDL for the tables, including all index definitions.

Would we need to?  A single math calc vs iterating thru string generations and comparisons?

Sure, it's above; I posted it years ago, when this q was first asked.

Actually I should adjust it again, you only need to calc the very first Friday,...

Just out of curiosity, what is the recovery model on the model db?

I'm wondering if the db got created then restored over ?!

You don't need to iterate/loop thru values looking for 'Tuesday', and personally I wouldn't.  I find all such code much harder to adjust later, and generally less efficient.

I guess you...

• This reply was modified 4 years, 5 months ago by ScottPletcher. Reason: Adjusted code to add variable for relative week# within month

https://techcommunity.microsoft.com/t5/sql-server/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537

Table IndexesRows in table   Hints               With or Without TF...

https://techcommunity.microsoft.com/t5/sql-server/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537

Table IndexesRows in table   Hints               With or Without TF 610 ...

Sure, it's above; I posted it years ago, when this q was first asked.

Actually I should adjust it again, you only need to calc the very first Friday, from then...

• This reply was modified 4 years, 5 months ago by ScottPletcher.
• This reply was modified 4 years, 5 months ago by ScottPletcher.
• This reply was modified 4 years, 5 months ago by ScottPletcher.

Fwiw in 2021.  In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month.  Then in the WHERE...

I believe the calc should be:

CAST(((f.TOTFCST - f.TOTHIST) * 100.00) / f.TOTHIST AS decimal(5, 2))

I have no idea at all why you are subtracting 1 from the quotient in your...

Fwiw in 2021.  In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month.  Then in the WHERE clause...

If you add the clustering before you add the data, make sure that you use WITH(TABLOCK) on the INSERT INTO statement to help take advantage of "Minimal Logging", which...

You need to cluster tables #SmallerRange and #LargerRange on the the JOIN columns: ( VID, IorO, OtherLocationID ).  If those 3 column values are unique in one/both of the tables,...

SELECT OBJECT_NAME(object_id) AS object_name, OBJECTPROPERTYEX(object_id, 'BaseType') AS object_type

FROM sys.sql_modules

WHERE definition LIKE N'%<string_you''re_looking_for>%'

ORDER BY 2, 1