• You can start with taking out the TOP 1's and see where that gets you. You might still have some restructuring to do, though. I would try to move most of the (SELECT FROM WHERE) clauses in the select statement to the FROM, so your select can be greatly simplified. This is a rough start of what I'm suggesting:

    SELECT

    t4.date_received

    ,t4.create_user_id

    ,t4.create_date

    ,CAST(t4.comments AS varchar(MAX)) AS comments

    , CASE WHEN t2.role_sk = 3936 AND ISNULL(last_name, '') <> ''

    THEN last_name

    WHEN t2.role_sk = 4310 AND ISNULL(last_name, '') <> ''

    THEN last_name

    WHEN t2.role_sk = 4216 AND ISNULL(last_name, '') <> ''

    THEN last_name

    ELSE '' END AS last_name

    , etc...

    FROM

    cases AS t1

    INNER JOIN case_parties t2 on t1.case_sk = t2.case_sk

    INNER JOIN legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk

    INNER JOIN comments AS t4 ON t1.case_sk = t4.case_sk

    WHERE

    (date_received BETWEEN @StartDate AND @EndDate)