The problem is that your JOINS are duplicating the rows because of the one-to-many relationship with tables C and D.

I'm leaving here 3 options that you might consider. On the first one, I just commented the joins and the problem was solved. The other 2 avoid the subqueries in the column list and use them in the FROM clause with JOIN or APPLY.

Be sure to understand the method you choose and if you have any questions, feel free to ask.

select a.ACTINDX as Actindex,

a.ACTNUMST,

b.ACTDESC,

(case when a.ACTINDX in (select ACTINDX from #TableC ) then 'Yes' else 'No' end) as 'TransHistCurrent',

(case when a.ACTINDX in (select ACTINDX from #TableD ) then 'Yes' else 'No' end) as 'TransHistHistorical'

from #TableA a

--Left outer join #TableC c on c.ACTINDX = a.ACTINDX

--Left outer join #TableD d on d.ACTINDX = a.ACTINDX

left outer join #TableB b on b.ACTINDX = a.ACTINDX

SELECT a.ACTINDX as Actindex,

a.ACTNUMST,

b.ACTDESC,

case when c.ACTINDX IS NULL then 'No' else 'Yes' end as 'TransHistCurrent',

case when d.ACTINDX IS NULL then 'No' else 'Yes' end as 'TransHistHistorical'

FROM #TableA a

LEFT OUTER JOIN #TableB b on b.ACTINDX = a.ACTINDX

LEFT OUTER JOIN( SELECT ACTINDX FROM #TableC GROUP BY ACTINDX ) c ON c.ACTINDX = a.ACTINDX

LEFT OUTER JOIN( SELECT ACTINDX FROM #TableD GROUP BY ACTINDX ) d ON d.ACTINDX = a.ACTINDX

SELECT a.ACTINDX as Actindex,

a.ACTNUMST,

b.ACTDESC,

case when c.ACTINDX IS NULL then 'No' else 'Yes' end as 'TransHistCurrent',

case when d.ACTINDX IS NULL then 'No' else 'Yes' end as 'TransHistHistorical'

FROM #TableA a

LEFT OUTER JOIN #TableB b on b.ACTINDX = a.ACTINDX

OUTER APPLY( SELECT TOP 1 ACTINDX FROM #TableC c1 WHERE c1.ACTINDX = a.ACTINDX) c

OUTER APPLY( SELECT TOP 1 ACTINDX FROM #TableD d1 WHERE d1.ACTINDX = a.ACTINDX) d