Definitely better than using cursors.

I lean toward them more than subqueries.

I prefer them over using temp tables in most cases.

I can probably do a quick google search for some...

Actually CTEs tend to be better for performance.

Downsides are that they can be difficult to learn at first.

They can reference themselves which is awesome but you can get stuck in...

The first and the second query should give you the same results.

I just did some consolidation. All the "magic" is in the "on" criteria.

Pretty sure it can be done without...

Try replacing exec (@sql) with select @sql to see what's actually running in the app.

For some reason, I remember reading something about not not using exec (@sql). Can't...

I think I've ran into a similar issue.

I had to trace through the code.

If you're using dynamic sql, I assume you are using sp_executeSQL @withsomeparameter ?

If so I would check...

Check your max - min is actually > 290:

SELECT @max-2,@min, @max-@min

You have no else statement

Also it looks to me like you have an extra "end" at the end

Are...

let me try this again

;

WITH newtable

AS (...

yep, that data structure is really confusing

Can you give an example of the expected output?

Or would the insert go into the #storeproducts table?

try this and let me know if it...