Improve plan_
Thanks
That said, the plan is full of hash match joins (I count 24), many of them against the same tables repeatedly. I am sure there are ways to consolidate this logic, but my first instinct would be to dump some of this data to #temp tables first, and massage it there, before introducing these parts of the logic into the overarching optimization.
There are a lot of implicit converts on rate_code.rate_unit. What is the underlying data type? Why is it being treated like nvarchar(12)? This isn't happening in the query; I suspect there is an underlying view that is causing this (but I can't see the view definitions to confirm).
There are a series of repeated operations where there is an inequality filter: payment_detail.invoice_module_id not in (412,560). If this is a common filter criteria and not something that just happened to be observed on this single iteration, a covering filtered index might make more sense, and you could eliminate all those rows from the query altogether (I am not sure if rows where all those values are 0 make any sense to include in the report). This could also help eliminate the key lookup, though I'm not sure if this on its own would have a dramatic impact to the overall query performance.
There are also a bunch of expressions that start out with case when 0 = 1 then – I don't understand the purpose of those.
Finally, the report returns 25,000 rows, and orders them by expressions that almost certainly can't be materialized until the query runs. Is there any possibility that (a) the report can consume far fewer rows at once, and implement paging or other techniques to consume more rows, and/or (b) the ordering could be based on underlying columns rather than the resulting expressions (which wouldn't change the ordering directly – NULL and 0 would both sort first).
Those are just a few ideas off the cuff, without having any understanding of the underlying hardware (current or potential), and without seriously trying to reverse engineer the query logic at all (if this is put together by a query builder or ORM of some kind, you may not have much control over that part of it anyway).