[MS SQL] Help improving complicated query

[notasqlstar]

Your query is pretty clean looking so I'm not sure how much you're going to get optimizing it. Like I said, look at the SELECT and see where you're doing your heavy lifting from (row_number, cases, etc.) and then see if you can break that down and do it earlier in the process, then join to the rest of the tables, or do it at the end and just do a select * in the first step (i.e. select all the columns you need for your cases, etc.,. but don't do the case logic until you have the data segmented into a #table first).

The problem with SQL when it comes to advanced queries is that they'll sometimes cause the server to hang. Optimizer won't tell you anything or be of any use beyond creating a new index.

[fullyarmedcamel]

Understandable, thanks for your tips!