recent things done to an important and complex sql query to improve performance

1. changed cross- apply to cte's
2. added indices that had all the output columns needed instead of using clustered index which forced more reads
3. removed max varchar columns from initial query to avoid key lookups and retrieved them at end of sql