...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

UPDATE STATISTICS [fctWMAUA]

SET SHOWPLAN_TEXT ON

select cur.SourceSystemKey, cur.date
from fctWMAUA cur
left join fctWMAUA next
    on next.SourceSystemKey = next.SourceSystemKey
    and next.date > cur.date
where next.SourceSystemKey is null
and cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)

SELECT * FROM
(
    SELECT MAX(t0.AsAtDateKey) AS [Date], t0.SourceSystemKey AS SourceSystem
    FROM fctWMAUA (NOLOCK) AS t0
    GROUP BY t0.SourceSystemKey
)
WHERE SourceSystem in (1,2,3,4,5,6,7,8,9)

SELECT [t3].[value]
FROM [dimSourceSystem] AS [t0]
OUTER APPLY (
    SELECT MAX([t2].[value]) AS [value]
    FROM (
        SELECT [t1].[AsAtDateKey] AS [value], [t1].[SourceSystemKey]
        FROM [fctWMAUA] AS [t1]
        ) AS [t2]
    WHERE [t2].[SourceSystemKey] = ([t0].[SourceSystemKey])
    ) AS [t3]

WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]

WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)

WHERE SourceSystemKey BETWEEN 1 AND 9

WHERE SourceSystemKey >= 1 AND SourceSystemKey <= 9

SELECT MAX(AsAtDateKey) AS [Date], SourceSystemKey AS SourceSystem
FROM fctWMAUA (NOLOCK)
GROUP BY SourceSystemKey
HAVING SourceSystemKey in (1,2,3,4,5,6,7,8,9)