As you have not provided the logic for your 'business month end' calculation, I'm not sure how you are expecting anyone to calculate it.

EOMONTH() gives you end of month...

maybe this but not guaranteed.

drop table if exists #TEMP1;
drop table if exists #TEMP2;
drop table if exists #TEMP3;

select distinct T1.QUOTE_DATE
into #TEMP1
from dbo.OptionsEOD T1

CREATE CLUSTERED INDEX #CI_TEMP1 ON...

try the following code.

and then give us the actual explain plan for both this code and your code.

drop table if exists #TEMP1;
drop table if exists #TEMP2;

select...

not much you can do about that query (which is the version I gave you) - the nature of the query does not really allow for pre-filtering or use...

no problem

here you...

Did...

no problem

here you go

https://www.brentozar.com/pastetheplan/?id=B1Ons1TUn

thanks for all the replies.  much appreciated.

Some background...I have been making changes to a stored proc.  I made a change (not sure exactly what tho) that resulted in incorrect query...

regarding your last query (from the second plan). two options below

option 1 is just a adjustment of your original query - does same thing but with 1 less join....

can you try this slight variation of my second example and give runtime and plan as well.

drop table if exists #weeklastday;
drop table if exists #temp1;

select distinct...

• This reply was modified 2 years, 5 months ago by water490.

that plan is from you version of the change e.g. where you still keep the dateadd and your temp table does not filter or change the expiration date.

can you...

there is no official cut off. the lowest possible is best.  the run time for the entire 1000 line...

small error on my original code - the temp table was referring an non existing alias (t1)- easy to fix I think.

another attempt at simplifying it assuming that I...

no difference.

your code was only looking for the the friday date on a particular situation e.g. when the EXPIRATION = Thursday

so by determining all the FRIDAY dates in advance,...

no difference.

your code was only looking for the the friday date on a particular situation e.g. when the EXPIRATION = Thursday

so by determining all the FRIDAY dates in advance,...