Exclude record if in a column

[blanchard306]

OK i'm having an issue with the bold part of the code. I'm trying to find which records that don't have NPP under their Profile column at all. The way that table is set up it has the Agent Id as a column and the Agent profile as a column. One Agent can have multiple profiles. I need to know if they Agent ID has NPP at all if so I need to exclude them from the results.


declare
@PolicyStartDate as decimal(8) = '20170101',
@policyEndDate as decimal(8) = '20170531'


SELECT DISTINCT [sublvo].[AGTHIST].[AGENT_ID], [sublag].[PLAN_Table].[PLN_DESC],[POLICY_EFFECT_DTE], [sublvo].[AGTHIST].[POLICY_NUMBER],[sublvo].[PCR].[QUALIFICATION_CODE]
FROM [sublvo].[AGTHIST] WITH (NOLOCK)
JOIN [sublag].[AGENT_SPONSOR_REL] WITH (NOLOCK) ON [sublag].[AGENT_SPONSOR_REL].[STAT_NUM] = [sublvo].[AGTHIST].[AGENT_ID]
join [sublvo].[PCR] WITH (NOLOCK) ON [LVOPROD].[AGTHIST].[POLICY_NUMBER] = [sublvo].[PCR].[POLICY_NUMBER]
Join [sublvo].[ACR] WITH (NOLOCK) ON [sublvo].[AGTHIST].[AGENT_ID] = [sublvo].[ACR].[AGENT_ID]
JOIN [sublag].[PLAN_Table] WITH (NOLOCK) ON [sublvl].[AGTHIST].[COVERAGE_PLAN_CODE] = [sublag].[PLAN_Table].[PLN_CD]
WHERE [sublag].[AGENT_SPONSOR_REL].[DISTBR_CD] = '360' AND ([sublag].[PLAN_Table].[PLN_DESC] LIKE 'CUSTOM%' OR [PLN_DESC] LIKE 'SELECT%')
AND [sublvo].[ACR].[AGENT_PROFILE] NOT EXISTS (Select sublvo.ACR.AGENT_ID from sublvo.ACR where sublvo.ACR.AGENT_PROFILE = 'NPP') AND [sublvo].[PCR].[POLICY_EFFECT_DTE] BETWEEN @PolicyStartDate AND @policyEndDate
ORDER BY [sublvo].[AGTHIST].[AGENT_ID];