We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SELECT column1,'99' [a],left(column2,2) [b], max(column3) [c], 'Yes' [d], 'Test' [e]
       ,ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as test
INTO #pre1       
FROM table1 
WHERE column4 = 'Yes'
GROUP BY column1, left(column2,2)

SELECT DISTINCT ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as test 
INTO #pre2
FROM table2

SELECT p1.column1, p1.a, p1.b, p1.c, p1.d, p1.e
FROM #pre1 p1
WHERE NOT EXISTS(SELECT 1 FROM #pre2 p2 WHERE p2.test = p1.test)

We walk in the dark places no others will enter
We stand on the bridge and no one may pass