At first sight, the query plan does not look like it does anything weird. No bad estimates, no strange operators where I would not expect them. You are simply doing a lot of work on a lot of rows, and that will take some time.
That being said, there might still be some things you can do.
First, the execution plan tells you that it is running into issues with data types; various columns have to be implicitly converted to other data types. You might want to look into where those are coming from. I think they are related to the CONCAT expressions in the final SELECT and in that case they won't affect performance, but it never hurst to check.
Second, you repeat a very complex query twice, with UNION ALL. The first time it selects all rows; the second time it only selects rows with SampleCondition NOT NULL, then does the exact same joins with those rows, and returns the same data except that the SampleCondition is replaced with the fixed value 4. This might not be the best way to do this. Instead of repeating the entire subquery, you can: a) Do a UNION ALL between only the Sample table and the non-NULL rows from the Sample table; then join all other columns to the result of that; or b) Replace the entire UNION ALL logic with a CROSS APPLY to a subquery that reads SELECT a.SampleCondition UNION ALL SELECT 4 WHERE a.SampleCondition IS NOT NULL
