I gave this a bit more thought...

Returning the MIN value of STRIKE will not work.  Instead, do the following...

If the sub-query ever returns an empty result then return the result...

Run the select without the insert, what type of values are you getting for what would be inserted into STRIKE_CONTRIBUTION?

Is anything in double digits, 99.9999999999999999

If so your numeric(28,27) is...

It would be simpler and more efficient to just calc the 3rd Friday of the month and then see if there are any rows for that date, i.e., you...

• This reply was modified 1 year, 7 months ago by water490.
• This reply was modified 1 year, 7 months ago by water490.

One more thing...

The create table and create index code was created before any rows were added to the temp tables.  Not sure why the run time grew like that.   Any...

I added the clustered indexes to the temp tables.  the run time is close to 2.5 minutes.  what!  before the change the query was running in 20 seconds.  what happened??

Thank you!

posting error

• This reply was modified 1 year, 7 months ago by water490.
• This reply was modified 1 year, 7 months ago by water490.
• This reply was modified 1 year, 7 months ago by water490.
• This reply was modified 1 year, 7 months ago by water490.

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big...

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages of...

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages of putting you...

You did some...

The optimiser should be able to simplify the query to remove the nesting so it might not make much difference.

One of the big advantages of putting you results into...

For some reason, you didn't ask -- perhaps deliberately -- about the simplest and probably the best option, which is just:

SELECT field1,field2
FROM table1

Because there is no...

thank you everyone!

I used the script that Jonathan provided.  It turns out that temp was using float when I used numeric.  Once I made this change the numbers...

thank you everyone!

I used the script that Jonathan provided.  It turns out that temp was using float when I used numeric.  Once I made this change the numbers match.