Duplicate of https://www.sqlservercentral.com/forums/topic/refinement-of-my-query-in-a-best-way-with-optimum-results-for-large-data

Is your data really so bad that all those predicates could be null or empty strings? Do they really contain invalid commas?

All the ISNULL(), REPLACE(), & LOWER() functions make your...

• This reply was modified 3 years, 6 months ago by ratbak.

Note that query would still return a row for every different combination of the columns. If all you're trying to get is the sum, you need to change the group...

Then none of those columns in any of the rows evaluate to valid NUMERIC(10) values. e.g., they might have spaces, punctuation, or alpha characters... or actually be null.

Do a select...

Can you provide an actual execution plan?

How many rows are in tblPolMaster? tblPolSpec?

How many rows are returned by the query?

Is tblPolSpec a generic Entity-Attribute Value lookup table? Do you have...

Why are all the columns being selected via subqueries rather than joins?

It's just a filter:

WHERE getdate() >= glfStartDate AND getdate() <= glfEndDate

Based on other articles, it appears you can change the data type of a column not used in the partitioning function/primary key.

Parentheses.

You probably want

and ([Error] LIKE 'Delivery Flushed' 
OR [Error] LIKE '%offline%' 
OR [Error] LIKE '%online%')

That's Oracle, not TSQL.

What's the error?

NVL2 (or COALESCE) seems like the better option anyway -- simpler, clearer.

Yes, I have the fnTally function.  When I run your code the way it is, it errors "Invalid column name 'Number'

Sorry. His version uses "N". I modified my version to...

Do you have Jeff's fnTally function and did you test it? It is a TVF that in this case returns numbers 1-52...

after I fixed the script to return Number + 1...

You can create a view using linked server tables, including with joins to local tables. You can't create a view including linked server tables with schemabinding.

Something like this using a tally table or function:

DECLARE @startDate date = '2021-01-06'
DECLARE @nextYear date = DATEADD(year,1,DATEADD(year,DATEDIFF(year,0,@startDate),0));
SELECT DATEADD(WEEK,Number,@startDate ) AS StartDate, DATEADD(DAY,6,DATEADD(WEEK,Number,@startDate )) AS EndDate, Number +...

No.

Amazon recommends using their Data Migration Service for this scenario.