Execution of the query below takes roughly 20 mins. qry1 has nearly 3 million records. The other table sizes are about 30k each. The problem is certainly at the 'NOT IN' point, but I'm not familiar enough with 'EXISTS' or other methods to speed this up.
J1.Provider_Type_Description = 'C'
J1.Provider_Type_Description = 'H'
WHEN J1.Provider_Id NOT IN (SELECT T1.Provider_ID FROM qry1 T1
INNER JOIN qry2 J1
ON J1.Provider_Id = T1.Provider_Id
INNER JOIN qry3 J2
ON J1.Specialisation_Description = J2.Specs)
END as Type_1
FROM qry0 T1
(SELECT DISTINCT Provider_Id, Provider_Name, Provider_Type_Description, provider_owner_name FROM qry4) AS J1
T1.provider_id = J1.provider_id