Consulting

Results 1 to 1 of 1

Thread: Exclude record if in a column

  1. #1

    Exclude record if in a column

    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];
    Last edited by Aussiebear; 04-21-2023 at 07:20 PM. Reason: Added code tags

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •