This is cosmetic, but all these nested subselects could be tidied up with a few inner joins

Thus we would replace

-----------

and T3689065.serial not in (

select serial from T3689065.dbo.responses2 where

variableid = (select variableid from T3689065.dbo.variables where variablename = 'DataCollection.Status')

and response = (

select cat_dbvalue from utilities.dbo.mdm_categoricals where

id_project = (select id_project from utilities.dbo.orc_projects where project_spss = 'T3689065')

and cat_name = 'Test'

and cat_parentname = 'DataCollection.Status'

)

------

with

----------------

and T3689065.serial not in (

select serial from T3689065.dbo.responses2 r2

inner join T3689065.dbo.variables v

on r2.variableid=v.variableid and v.variablename = 'DataCollection.Status'

inner join utilities.dbo.mdm_categoricals cat

on r2.response=cat.cat_dbvalue and cat.cat_name = 'Test'

and cat.cat_parentname = 'DataCollection.Status'

inner join utilities.dbo.orc_projects proj on r2.id_project=proj.id_project and proj.project_spss = 'T3689065'------------