the queries are giving the correct output now.

thank you both so much!

Thank you both!!  I appreciate your help so much!

I tested another scenario.  I added a different company in the list so there are now two not one company.

Drew, your solution...

• This reply was modified 7 months, 4 weeks ago by  water490.

Thank you everyone!

I will try them and report back any issues

Thank you everyone.

This is very interesting.  Is this a bug within SS that you cannot drop a #temp table inside a SP using the DROP TABLE command?  Or is there...

one thing I should mention/clarify...

each time I re-use #temp2 I am using a completely different select statement so the columns going into #temp2 will be different.

it is odd that I...

thank you everyone for your feedback.  I will test the code and see how the output looks

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 8 months, 2 weeks ago by  water490.
• This reply was modified 8 months, 2 weeks 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 8 months, 2 weeks ago by  water490.
• This reply was modified 8 months, 2 weeks ago by  water490.
• This reply was modified 8 months, 2 weeks ago by  water490.
• This reply was modified 8 months, 2 weeks 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...