Exclude records with column value starting with '836'

  • daniness

    SSCrazy

    Points: 2890

    Hi All,

    So my latest issue is I need to be able to exclude records with claim numbers that start with '836'. This seems pretty straight forward, but nothing I've tried seems to be working, as I'm still getting back claim numbers starting with '836'. I've tried using NOT LIKE and NOT IN. Could I please be advised what I'm doing wrong? I'm including the query and my comments. Thanks in advance for your input.

    DECLARE @StartDate AS DateTime = '1/1/2017'

    IF OBJECT_ID('tempdb..##ProducerCommBridge') IS NOT NULL
    DROP TABLE ##ProducerCommBridge;

    SELECT DISTINCT [ProducerCommBridgeKey]
    ,[PolicyAgreementNumber]
    ,[CommissionStartDate]
    ,[CommissionEndDate]
    INTO ##ProducerCommBridge
    FROM [dbo].[DimProducerCommBridge]
    ;

    IF OBJECT_ID('tempdb..##FctLossData') IS NOT NULL
    DROP TABLE ##FctLossData;

    CREATE TABLE ##FctLossData (
    [QIPS_LPRG_SID] numeric(10,0) null,
    [PACClaimID] nvarchar(50) NULL,
    [EquipmentID] int null,
    [ClaimID] [int] NOT NULL,
    [ClaimNumber] [varchar](34) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [LossDate] [datetime] NOT NULL,
    [ProCloseDate] [datetime] NULL,
    [ProducerCommBridgeKey] [int] NOT NULL,
    [ClaimStatus] [varchar](12) NULL,
    [ContractNumber] [varchar](30) NULL,
    [PolicyID] [varchar](20) NULL,
    [CustomerID] [nvarchar](15) NULL,
    [ProductID] [nvarchar](5) NULL,
    [CatastropheID] [int] NULL,
    [InsuranceClassCode] [varchar](1000) NULL,
    [EquipmentName] [varchar](1000) NULL,
    [EquipmentClassName] [varchar](1000) NULL,
    [EquipmentDescription] [nvarchar](1000) NULL,
    [RegionCode] [nvarchar](2) NULL,
    [CountryCode] [nvarchar](3) NULL,
    [ClaimLossType] [nvarchar](50) NULL,
    [CauseOfLoss] [nvarchar](255) NULL,
    [EnrollmentID] [nvarchar](50) NULL,
    [CoverageID] [int] NULL,
    [CoverageEffectiveDate] [datetime] NULL,
    [CustomerAccountNumber] [nvarchar](50) NULL,
    [PaidLossAmount] [money] NULL,
    [CaseReserveAmount] [money] NULL,
    [RecoveryReserveAmount] [money] NULL,
    [SalvageRecoveryAmount] [money] NULL,
    [SubroRecoveryAmount] [money] NULL,
    [TotalRecoveryAmount] [money] NULL,
    [AllocatedLAEAmount] [money] NULL
    )
    ;

    INSERT INTO ##FctLossData

    --DECLARE @StartDate AS DateTime = '1/1/2017'

    SELECT
    qle.LPRG_SID
    ,lc.PACClaimID
    ,ems.EquipmentID
    ,c.ClaimID
    ,(c.ClaimPrefix + '-' + CONVERT(varchar, c.ClaimNumber)) AS ClaimNumber
    ,CONVERT(date, CreateDate) AS CreateDate
    ,CONVERT(date, c.LossDate) AS LossDate
    ,CASE WHEN DATEPART(hh, EffectiveDate) < 17 then CONVERT(date, EffectiveDate) ELSE CONVERT(date, dateadd(dd, 1, EffectiveDate)) END as ProCloseDate
    ,-1 AS ProducerCommBridgeKey
    ,cs.ClaimStatus
    ,UPPER(LEFT(c.ContractNumber, 10)) AS ContractNumber
    ,CASE WHEN c.ContractNumber IS NOT NULL THEN LEFT(c.PolicyNumber, 7) ELSE c.PolicyNumber END AS PolicyID
    ,sCustomerID AS CustomerID
    ,CASE WHEN pro.sProductID = '9095' THEN '1095' ELSE pro.sProductID END AS ProductID
    ,c.CatastropheID
    ,CASE WHEN LTRIM(RTRIM(dbo.fn_clean_string(InsuranceClassCode))) = '' THEN 'UNKNOWN' ELSE COALESCE(UPPER(LTRIM(RTRIM(dbo.fn_clean_string(InsuranceClassCode)))), 'UNKNOWN') END AS InsuranceClassCode
    ,CASE WHEN LTRIM(RTRIM(dbo.fn_clean_string(EquipmentName))) = '' THEN 'UNKNOWN' ELSE COALESCE(UPPER(LTRIM(RTRIM(dbo.fn_clean_string(EquipmentName)))), 'UNKNOWN') END AS EquipmentName
    ,CASE WHEN EquipmentClassName IS NULL THEN 'UNKNOWN' ELSE UPPER(LTRIM(RTRIM(dbo.fn_clean_string(EquipmentClassName)))) END AS EquipmentClassName
    ,CASE WHEN EquipmentDescription IS NULL THEN 'UNKNOWN' ELSE UPPER(LTRIM(RTRIM(dbo.fn_clean_string(EquipmentDescription)))) END AS EquipmentDescription
    ,RegionCode
    ,CountryCode
    ,CASE WHEN ClaimLossType IS NULL THEN 'Unknown' ELSE ClaimLossType END AS ClaimLossType
    ,CASE WHEN CauseOfLoss IS NULL THEN 'Unknown' ELSE CauseOfLoss END AS CauseOfLoss
    ,CASE WHEN qc.CLAM_LPRG_SID IS NOT NULL THEN CONVERT(nvarchar(50), qle.LEAS_SID) WHEN lc.EnrollmentID IS NOT NULL THEN CONVERT(nvarchar(50), lc.EnrollmentID) ELSE NULL END AS EnrollmentID
    ,NULL AS CoverageID
    ,NULL AS CoverageEffectiveDate
    ,CASE WHEN qc.CLAM_LPRG_SID IS NOT NULL THEN CONVERT(nvarchar(50), qle.LEAS_LEASE_CODE) WHEN lc.EnrollmentID IS NOT NULL THEN CONVERT(nvarchar(50), lefs.ContractNumber) ELSE NULL END AS CustomerAccountNumber
    ,SUM(CASE WHEN Reserve = 0 AND Recovery = 0 AND TranTypeID = 1 THEN Amount END) AS PaidLossAmount
    ,SUM(CASE WHEN Reserve = 1 AND Recovery = 0 AND TranTypeID = 1 THEN Amount END) AS CaseReserveAmount
    ,SUM(CASE WHEN Reserve = 1 AND Recovery = 1 AND TranTypeID IN (1, 7, 8) THEN Amount END) AS RecoveryReserveAmount
    ,SUM(CASE WHEN Reserve = 0 AND Recovery = 1 AND TranTypeID = 8 THEN Amount END) AS SalvageRecoveryAmount
    ,SUM(CASE WHEN Reserve = 0 AND Recovery = 1 AND TranTypeID = 7 THEN Amount END) AS SubroRecoveryAmount
    ,SUM(CASE WHEN Reserve = 0 AND Recovery = 1 AND TranTypeID IN (1, 7, 8) THEN Amount END) AS TotalRecoveryAmount
    ,SUM(CASE WHEN Reserve = 0 AND Recovery = 0 AND TranTypeID = 6 THEN Amount END) AS AllocatedLAEAmount
    FROM dbo.ExtPACTransaction t
    INNER JOIN dbo.ExtPACClaim c ON t.ClaimID = c.ClaimID
    --AND c.ClaimNumber NOT LIKE '836%'
    INNER JOIN dbo.ExtPRSDEPolicy pol ON (CASE WHEN [ContractNumber] IS NOT NULL THEN LEFT(c.PolicyNumber, 7) ELSE c.PolicyNumber END) = pol.sPolicyID
    INNER JOIN dbo.ExtPRSDECustomer cus ON pol.iCustomerKey = cus.iCustomerKey
    INNER JOIN dbo.ExtPRSDEProduct pro ON pol.iProductKey = pro.iProductKey
    LEFT OUTER JOIN dbo.ExtPACClaimStatus cs ON c.ClaimID = cs.ClaimID
    LEFT OUTER JOIN dbo.ExtJWProductSegment jw ON pro.sProductID = jw.sProductID
    LEFT OUTER JOIN dbo.ExtEMSEquipment ems ON c.EMIEquipmentID = CONVERT(varchar, ems.EquipmentNumber)
    LEFT OUTER JOIN dbo.ExtQIPSClaims qc ON qc.CLAM_PAC_CLAIMID = c.ClaimID
    LEFT OUTER JOIN dbo.ExtQIPSLeaseEnrollment qle ON qle.LPRG_SID = qc.CLAM_LPRG_SID
    LEFT OUTER JOIN dbo.ExtLP2Claim lc ON lc.PACClaimID = c.ClaimID
    LEFT OUTER JOIN dbo.ExtLP2EnrollmentForSearch lefs ON lefs.EnrollmentID = lc.EnrollmentID
    WHERE c.LossDate >= @StartDate
    --AND c.ClaimNumber NOT LIKE '836%' -- To remove FIS claims
    --AND c.ClaimNumber NOT IN (SELECT c2.ClaimNumber FROM ExtPACClaim c2 WHERE ClaimNumber LIKE '836%')
    AND c.isVoided = 0
    AND (pro.sProductID = '9095' OR jw.sProductID IS NOT NULL)
    GROUP BY qle.LPRG_SID
    ,lc.PACClaimID
    ,ems.EquipmentID
    ,c.ClaimID
    ,(c.ClaimPrefix + '-' + CONVERT(varchar, c.ClaimNumber))
    ,CONVERT(date, CreateDate)
    ,CONVERT(date, c.LossDate)
    ,CASE WHEN DATEPART(hh, t.EffectiveDate) < 17 then CONVERT(date, t.EffectiveDate) ELSE CONVERT(date, dateadd(dd, 1, t.EffectiveDate)) END
    ,cs.ClaimStatus
    , UPPER(LEFT(c.ContractNumber, 10))
    ,CASE WHEN c.ContractNumber IS NOT NULL THEN LEFT(c.PolicyNumber, 7) ELSE c.PolicyNumber END
    ,sCustomerID
    ,CASE WHEN pro.sProductID = '9095' THEN '1095' ELSE pro.sProductID END
    ,c.CatastropheID
    ,CASE WHEN LTRIM(RTRIM(dbo.fn_clean_string(InsuranceClassCode))) = '' THEN 'UNKNOWN' ELSE COALESCE(UPPER(LTRIM(RTRIM(dbo.fn_clean_string(InsuranceClassCode)))), 'UNKNOWN') END
    ,CASE WHEN LTRIM(RTRIM(dbo.fn_clean_string(EquipmentName))) = '' THEN 'UNKNOWN' ELSE COALESCE(UPPER(LTRIM(RTRIM(dbo.fn_clean_string(EquipmentName)))), 'UNKNOWN') END
    ,CASE WHEN EquipmentClassName IS NULL THEN 'UNKNOWN' ELSE UPPER(LTRIM(RTRIM(dbo.fn_clean_string(EquipmentClassName)))) END
    ,CASE WHEN EquipmentDescription IS NULL THEN 'UNKNOWN' ELSE UPPER(LTRIM(RTRIM(dbo.fn_clean_string(EquipmentDescription)))) END
    ,RegionCode
    ,CountryCode
    ,CASE WHEN ClaimLossType IS NULL THEN 'Unknown' ELSE ClaimLossType END
    ,CASE WHEN CauseOfLoss IS NULL THEN 'Unknown' ELSE CauseOfLoss END
    ,CASE WHEN qc.CLAM_LPRG_SID IS NOT NULL THEN CONVERT(nvarchar(50), qle.LEAS_SID) WHEN lc.EnrollmentID IS NOT NULL THEN CONVERT(nvarchar(50), lc.EnrollmentID) ELSE NULL END
    ,CASE WHEN qc.CLAM_LPRG_SID IS NOT NULL THEN CONVERT(nvarchar(50), qle.LEAS_LEASE_CODE) WHEN lc.EnrollmentID IS NOT NULL THEN CONVERT(nvarchar(50), lefs.ContractNumber) ELSE NULL END

    --ORDER BY ClaimNumber DESC
    ;

    --To view records
    SELECT *
    FROM ##FctLossData
    WHERE ClaimNumber LIKE '836%'

     

  • Phil Parkin

    SSC Guru

    Points: 244578

    NOT LIKE '836%'

    works. If you think it doesn't, please provide a query which we can run to prove otherwise.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • DesNorton

    SSC-Insane

    Points: 23055

    Let's work backwards on this.

    To view records that you are trying to filter out

    SELECT *
    FROM ##FctLossData
    WHERE ClaimNumber LIKE '836%'

    However, ClaimNumber is made up of 2 fields

    INSERT INTO ##FctLossData ...
    SELECT ...
    ,(c.ClaimPrefix + '-' + CONVERT(varchar, c.ClaimNumber)) AS ClaimNumber
    WHERE c.LossDate >= @StartDate
    --AND c.ClaimNumber NOT LIKE '836%' -- To remove FIS claims

    So, maybe you should be filtering on ClaimPrefix rather than ClaimNumber

    WHERE c.LossDate >= @StartDate
    AND c.ClaimPrefix NOT LIKE '836%' -- To remove FIS claims

    Considering the number of joins, the fact that it is an inner join, and the logical query processing order, I would be inclined to add the filter to the JOIN clause in order to eliminate the filtered records earlier.  However, you would need to test the impact with your own data and environment.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply