Need Help with this plan ( Query is running more than 1 Hour)
First, you have a couple clustered index scans. That means every row in the table will be scanned. That could be a LOT of data. Can you add additional WHERE clause or JOIN clause filters to transform the scan into a seek?
Second, you have some HUGE data reads on the lower right branch of the query plan, meaning 10s of millions of records, all acquired through index scans. It looks like your seek predicates are being thwarted. Both of those indexes are composite indexes made up of multiple columns. You might wish to create individual non-clustered indexes on each column or more closely evaluate the scan criteria to see what better kind of index will help speed this query.
Hope that helps,