;with cte as
(
Select C_SSKey, MAX(DS_SysEnd) MaxDate
from #PriorMonth
group by C_SSKey
)
select c.* , cte.MaxDate
from #CurrentMonth C
inner join cte on cte.C_SSKey = c.C_SSKey
Does this work? If not, please specify how you want the output to look given your test data.
I'll try that, in the meantime, I believe I solved my issue using Partition by and Row_Number