I've had to change column and table names for security reasons.
SELECT
COUNT(DISTINCT CASE WHEN UPPER(fee.blah) LIKE '%AMEP%' THEN c.blah ELSE NULL END) as DISTINCT_BLAH_COUNT,
MAX(c.blah) as blah
FROM dbo.TABLE1 a
INNER JOIN dbo.TABLE2 d ON a.blah = d.blah
INNER JOIN dbo.TABLE3 b ON a.blah = b.blah
INNER JOIN dbo.TABLE4 dt ON a.blah = dt.blah
INNER JOIN dbo.TABLE5 att ON a.blah = att.blah
INNER JOIN dbo.TABLE6 c ON a.blah = c.blah
INNER JOIN dbo.TABLE7 fc ON a.blah = fc.blah
INNER JOIN dbo.TABLE8 age ON a.blah = age.blah
LEFT JOIN dbo.TABLE9 ft ON a.blah = ft.blah
LEFT JOIN dbo.TABLE10 fee ON a.blah = fee.blah
LEFT JOIN dbo.TABLE11 ccs ON a.blah = ccs.blah
WHERE
d.blah in ('Centre','Discontinued')
AND dt.blah >= 2019
GROUP BY
a.blah,
c.blah,
fc.blah,
dt.blah,
dt.blah
But this is the simplest version I can make that is erroring. It's not really a complex query as you can see
Another errror happening occasionally is
Request to perform an external distributed computation has failed with error "Assertion failed: Degree of partitioned parallelism can only be 1 when the child operator distribution is full or when the session dop is 1".
