Joining on the numeric part of string...

  • Hi All,

    It's been a while...I'd like to get your advice on the most efficient way to join on only the number part of a field that may be prefixed and/or suffixed with up to 2 letters. Here's a simplified snippet of what I'm trying to do:

         SELECT a, b, c
         FROM table 1 t1
         LEFT JOIN table 2 t2 ON t1.PolicyCode = t2.sPolicyID,

    where t2.sPolicyID could begin and/or end with up to 2 letters, i.e. sample sPolicyID: TG73100, S7286674, 2344506R,etc.) and we only want to join to just its numeric part in between the letters, i.e. "73100", "7286674" or "2344506" from the examples. Could someone please advise on what would be a simple way of doing this? Thanks in advance! Smile

  • If you have control over the design of the table, you could create a new column and extract just that part to the new column. Then you could index it and the join would work pretty well. Otherwise, you're basically forcing a table scan of the column because indexes on it won't help anything.

  • daniness - Wednesday, July 11, 2018 2:31 PM

    Hi All,

    It's been a while...I'd like to get your advice on the most efficient way to join on only the number part of a field that may be prefixed and/or suffixed with up to 2 letters. Here's a simplified snippet of what I'm trying to do:

         SELECT a, b, c
         FROM table 1 t1
         LEFT JOIN table 2 t2 ON t1.PolicyCode = t2.sPolicyID,

    where t2.sPolicyID could begin and/or end with up to 2 letters and we only want to join to just its numeric part in between the letters. Could someone please advise on what would be a simple way of doing this? Thanks in advance! 🙂

    As pietlinden indicates, what you need here is a "computed" PERSISTED column.  That column can then be indexed, and then you can join directly to it.  You just need to define the new column using T-SQL and some combination of string functions that will extract only the numeric portion.   Post back the details and we can help you create that.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • pietlinden beat me to it - here's what that solution would look like:

    -- Sample data
    USE tempdb
    GO
    IF OBJECT_ID('dbo.t1') IS NOT NULL DROP TABLE dbo.t1;
    IF OBJECT_ID('dbo.t2') IS NOT NULL DROP TABLE dbo.t2;
    GO
    CREATE TABLE dbo.t1 (policyCode INT PRIMARY KEY); -- added PK for the index
    CREATE TABLE dbo.t2 (policyID VARCHAR(10));

    INSERT dbo.t1 VALUES (123),(222),(555);
    INSERT dbo.t2 VALUES ('AB123'),('123'),('123XX'),('X222'),('222TT'),('555')

    -- Add persisted computed column
    ALTER TABLE t2
    ADD polCode AS
    CASE
      WHEN policyID LIKE '[^0-9]%'
      THEN SUBSTRING(
        policyID,
        PATINDEX('%[0-9]%',policyID), 8000)
      ELSE SUBSTRING(
        policyID, 1,
        ISNULL(NULLIF(PATINDEX('%[^0-9]%',policyID), 0),8000)-1)
    END PERSISTED;

    -- Index the new computed column
    CREATE NONCLUSTERED INDEX nc_t2_xxx ON dbo.t2(polCode) INCLUDE (policyID);

    -- Efficient Query
    SELECT t1.policyCode,
       t2.policyID
    FROM dbo.t1 t1
    JOIN dbo.t2 t2 ON t1.policyCode = t2.polCode

    If you can't add a persisted column another solution would be to take the above CASE statement and use it to create an indexed view. If you can add a computed column then the best performance would from turning the final query into an indexed view:
    CREATE VIEW dbo.TTT
    WITH SCHEMABINDING AS
    SELECT t1.policyCode,
       t2.policyID
    FROM dbo.t1 t1
    JOIN dbo.t2 t2 ON t1.policyCode = t2.polCode
    GO

    CREATE UNIQUE CLUSTERED INDEX xxxx ON dbo.TTT(policyID,policyCode);
    Obviously I don't know your data so you would have to tweak this a bit. 

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • The way I read it, you could have letters on both the front and the back of the string.  This CASE code handles that situation as well:


    INSERT INTO dbo.t2 VALUES('AB345FG'),('ABCDEF');

    ;WITH cte_t2 AS (
      SELECT *,
          CASE WHEN first_digit = 0 THEN '' ELSE SUBSTRING(t2.policyID, first_digit,
              PATINDEX('%[^0-9]%', SUBSTRING(t2.policyID + '.', first_digit, 10)) - 1)
              END AS policyCode
      FROM dbo.t2 t2
      CROSS APPLY (
         SELECT PATINDEX('%[0-9]%', t2.PolicyID) AS first_digit
      ) AS alias1
    )
    SELECT *
    FROM dbo.t1 t1
    INNER JOIN cte_t2 t2 ON t2.policyCode = t1.policyCode

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thanks for all of your input, folks.

    @scottpletcher , I'm able to somewhat understand your solution, but could you please advise how I could fit it into this code? Would this work, and I apologize for the monstrosity this is. I'm not even sure how the BA was able to wrap his head around this query...I have great difficulty comprehending such ridiculously long and convoluted ones:

    SELECT
    QU

    .[LeasSID]

    ,CAST('QIPS' AS TEXT) as EnrollmentSys

    ,QU.[LeaseCode]

    ,QU.[PolicyCode]

    ,pp.sPolicyID

    ,pp.sInsuredName

    ,QU.[ClientID]

    ,QU.[ContractProfileID]

    ,QU.[CcovSID]

    ,QU.[CoverageCode]

    ,zp.ZIPcd

    ,QU.[PPPInsuranceMethodCode]

    ,QU.[EquipmentCode]

    ,QU.[CoverageEffectiveDate]

    ,QU.LeaseEndDate

    ,QU.[CoverageExpirationDate]

    ,QU.CancellationDate

    ,QU.[LeaseEffectiveDate]

    ,QU.[OriginalEndDate]

    ,QU.[FullTermCalc]

    ,QU.[ITDEarnedPremium]

    ,QU.MonthEndDate

    ,CLMSUMF.ClaimAmt

    ,QU.MONTH_END_DATE

    ,QCAA.COAP_APPLY_AMT

    FROM

    (SELECT

    qmr.[LeasSID]

            ,[LeaseCode]

            ,[PolicyCode]

            ,[ClientID]

            ,[ContractProfileID]

            ,[CcovSID]

            ,[CoverageCode]

            ,[PPPInsuranceMethodCode]

            ,[EquipmentCode]

            ,[CoverageEffectiveDate]

            ,[CoverageExpirationDate]

            ,qccd.[CCOV_CANCELLATION_DATE] AS CancellationDate

            ,[LeaseEffectiveDate]

            ,[LeaseExpirationDate] as [OriginalEndDate]

            ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate] ELSE qccd.[CCOV_CANCELLATION_DATE] END as [LeaseEndDate]

            ,[MonthEndDate]

            ,ftc.COMP_FULL_TERM_CALC_AMT AS [FullTermCalc]

            ,[ITDEarnedPremium]

            ,ftc.COMP_SID

            ,ftc.PTRN_SID

            ,ftc.PTRN_LPRG_SID

            ,ftc.MONTH_END_DATE

      FROM [dbo].[StgQIPSMonthlyRevenue] qmr

    join [dbo].[ExtQIPSClientCoverageDetail] QCCD on qmr.CcovSID = QCCD.CCOV_SID

    join (SELECT [LeasSID],max([MonthEndDate]) as maxreportdate

          FROM [dbo].[StgQIPSMonthlyRevenue]

          group by LeasSID) QMrs on qmr.LeasSID = QMrs.LeasSID

                             and qmr.MonthEndDate = QMrs.maxreportdate

    JOIN

    ( SELECT [COMP_CCOV_SID]

      ,COMP_SID

      ,QPT.PTRN_SID

      ,QPT.PTRN_LPRG_SID

      ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE AS MONTH_END_DATE

      ,[COMP_FULL_TERM_CALC_AMT]

      ,[FCGT_FUNDED_FLAG]

      FROM [dbo].[ExtQIPSCoverageCalcComps] QCCC

    join dbo.ExtQIPSClientCoverageDetail QCCD

         on QCCC.COMP_CCOV_SID = QCCD.CCOV_SID

    left join [dbo].[ExtQIPSPaymentTransactions] QPT

         on QCCD.CCOV_LPRG_SID = QPT.PTRN_LPRG_SID

         where FCGT_FUNDED_FLAG is null      

         GROUP BY [COMP_CCOV_SID]

                  ,COMP_SID

                  ,QPT.PTRN_SID

                  ,QPT.PTRN_LPRG_SID

                  ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE

                  ,[COMP_FULL_TERM_CALC_AMT]

                  ,[FCGT_FUNDED_FLAG]

    ) ftc ON qmr.CcovSID = ftc.COMP_CCOV_SID

    group by qmr.[LeasSID]

             ,[LeaseCode]

             ,[PolicyCode]

             ,[ClientID]

             ,[ContractProfileID]

             ,[CcovSID]

             ,[CoverageCode]

             ,[PPPInsuranceMethodCode]

             ,[EquipmentCode]

             ,[CoverageEffectiveDate]

             ,[CoverageExpirationDate]
             ,qccd.[CCOV_CANCELLATION_DATE]

             ,[LeaseEffectiveDate]

             ,[LeaseExpirationDate]

             ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate] ELSE qccd.[CCOV_CANCELLATION_DATE] END

             ,[MonthEndDate]

             ,ftc.COMP_FULL_TERM_CALC_AMT

             ,[ITDEarnedPremium]

             ,ftc.COMP_SID

             ,ftc.PTRN_SID

             ,ftc.PTRN_LPRG_SID

             ,ftc.MONTH_END_DATE

           UNION ALL

    SELECT qbr.[LeasSID]

           ,[LeaseCode]

           ,[PolicyCode]

           ,[ClientID]

           ,[ContractProfileID]

           ,[CcovSID]

           ,[CoverageCode]

           ,[PPPInsuranceMethodCode]

           ,[EquipmentCode]

           ,[CoverageEffectiveDate]

           ,[CoverageExpirationDate]

           ,qccd.[CCOV_CANCELLATION_DATE] AS CancellationDate

           ,[LeaseEffectiveDate]

           ,[LeaseExpirationDate] as [OriginalEndDate]

           ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate] ELSE qccd.[CCOV_CANCELLATION_DATE] END as [LeaseEndDate]

           ,[MonthEndDate]

           ,ftc.COMP_FULL_TERM_CALC_AMT AS [FullTermCalc]

           ,[ITDEarnedPremium]

           ,ftc.COMP_SID

           ,ftc.PTRN_SID

           ,ftc.PTRN_LPRG_SID

           ,ftc.MONTH_END_DATE

    FROM [dbo].[StgQIPSBaseRevenue] qbr

    join [dbo].[ExtQIPSClientCoverageDetail] QCCD on qbr.CcovSID = QCCD.CCOV_SID

    join

    (

    SELECT

    [LeasSID],max([MonthEndDate]) as

    maxreportdate

           FROM

    [dbo].[StgQIPSBaseRevenue]

           group by

    LeasSID

    )

    QBrs on qbr.LeasSID = QBrs.

    LeasSID

                                  and

    qbr.MonthEndDate = QBrs.maxreportdate

           JOIN ( SELECT [COMP_CCOV_SID]

                         ,COMP_SID

                         ,QPT.PTRN_SID

                         ,QPT.PTRN_LPRG_SID

                         ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE AS MONTH_END_DATE

                         ,[COMP_FULL_TERM_CALC_AMT]

                         ,[FCGT_FUNDED_FLAG]

                  FROM [dbo].[ExtQIPSCoverageCalcComps] QCCC

                  join dbo.ExtQIPSClientCoverageDetail QCCD

                         on QCCC.COMP_CCOV_SID = QCCD.CCOV_SID

                  left join [dbo].[ExtQIPSPaymentTransactions] QPT

                         on QCCD.CCOV_LPRG_SID = QPT.PTRN_LPRG_SID

                         where FCGT_FUNDED_FLAG is null      

                  GROUP BY [COMP_CCOV_SID]

                           ,COMP_SID

                           ,QPT.PTRN_SID

                           ,QPT.PTRN_LPRG_SID

                           ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE

                           ,[COMP_FULL_TERM_CALC_AMT]

                           ,[FCGT_FUNDED_FLAG]) ftc ON qbr.CcovSID = ftc.COMP_CCOV_SID 

    group by qbr.[LeasSID]

             ,[LeaseCode]

             ,[PolicyCode]

             ,[ClientID]

             ,[ContractProfileID]

             ,[CcovSID]

             ,[CoverageCode]

             ,[PPPInsuranceMethodCode]

             ,[EquipmentCode]

             ,[CoverageEffectiveDate]

             ,[CoverageExpirationDate]

             ,qccd.[CCOV_CANCELLATION_DATE]

             ,[LeaseEffectiveDate]

             ,[LeaseExpirationDate]

             ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate ELSE qccd.[CCOV_CANCELLATION_DATE] END
             ,[MonthEndDate]

             ,ftc.COMP_FULL_TERM_CALC_AMT

             ,[ITDEarnedPremium]  

             ,ftc.COMP_SID

             ,ftc.PTRN_SID

             ,ftc.PTRN_LPRG_SID

             ,ftc.MONTH_END_DATE) QU  

    ;WITH cte_pp AS ( SELECT *, CASE WHEN first_digit = 0 THEN '' ELSE SUBSTRING(pp.sPolicyID, first_digit, PATINDEX('%[^0-9]%',

                                                                       SUBSTRING(pp.sPolicyID + '.', first_digit, 10)) -END AS policyCode

                      FROM dbo.ExtPRSDEPolicy pp

    CROSS APPLY (SELECT PATINDEX('%[0-9]%', pp.sPolicyID) AS first_digit) AS alias1)

    SELECT * FROM QU
    IN
    NER JOIN cte_pp ctepp ON ctepp.policyCode = QU.policyCode   

    LEFT JOIN ( SELECT la.LADD_LEAS_SID

                       ,CASE WHEN Za.ZIPa IS NULL THEN Zb.ZIPb ELSE Za.ZIPa END ZIPcd

                FROM [dbo].[ExtQIPSLeaseAddresses] LA

                LEFT JOIN (SELECT LADD_LEAS_SID,
                          
    [LADD_POSTAL_CODE_TXT] AS ZIPa 
                           
    FROM [dbo].[ExtQIPSLeaseAddresses]
                          
    WHERE [LADD_ADDR_CODE]='E'

                           GROUP BY LADD_LEAS_SID,[LADD_POSTAL_CODE_TXT]) Za
               
    ON Za.LADD_LEAS_SID = LA.LADD_LEAS_SID

    LEFT JOIN (SELECT LADD_LEAS_SID,
              
    [LADD_POSTAL_CODE_TXT] AS ZIPb
             
    FROM [dbo].[ExtQIPSLeaseAddresses]
              
    WHERE [LADD_ADDR_CODE]='B'

               GROUP BY LADD_LEAS_SID,[LADD_POSTAL_CODE_TXT]) Zb
     
    ON Zb.LADD_LEAS_SID = LA.LADD_LEAS_SID

    GROUP BY  la.LADD_LEAS_SID

              ,CASE WHEN Za.ZIPa is null THEN Zb.ZIPb ELSE Za.ZIPa END) zp
    ON QU.[LeasSID] = zp.LADD_LEAS_SID
    LEFT OUTER JOIN (SELECT CLMSUM.CLAM_CCOV_SID,

                     SUM(CLMSUM.CLAIMAMT) AS ClaimAmt

                     FROM (SELECT [CLAM_CCOV_SID]
                                  ,[CLAM_PAC_CLAIMID]

                                  ,clm.CLAIMAMT

                           FROM [dbo].[ExtQIPSClaims] QC

                           join (SELECT [ClaimID]

                                        ,SUM([Amount]) AS CLAIMAMT

                                 FROM [dbo].[ExtPACTransaction]

                                 GROUP BY [ClaimID]) clm ON QC.[CLAM_PAC_CLAIMID] = clm.ClaimID) CLMSUM
                           G
    ROUP BY CLMSUM.CLAM_CCOV_SID) CLMSUMF ON QU.[CcovSID] = CLMSUMF.CLAM_CCOV_SID

                left join [dbo].[ExtQIPSCashApplyAmounts] QCAA on Qu.PTRN_LPRG_SID = QCAA.LPCA_LPRG_SID
                                                              
    and qu.COMP_SID = QCAA.COAP_COMP_SID

                                                              and Qu.PTRN_SID = QCAA.LPCA_PTRN_SID


                where year([CoverageEffectiveDate]) like '2001%' 
                     
    AND sPolicyID = '1035445'  

                order by LeasSID, sPolicyID














































































































































    Thanks for all of your input, folks.

    @scottpletcher , I'm able to somewhat understand your solution, but could you please advise how I could fit it into this code? Would this work, and I apologize for the monstrosity this is. I'm not even sure how the BA was able to wrap his head around this query...I have great difficulty comprehending such ridiculously long and convoluted ones:

    SELECT
    QU.[LeasSID]

    ,CAST('QIPS' AS TEXT) as EnrollmentSys

    ,QU.[LeaseCode]

    ,QU.[PolicyCode]

    ,pp.sPolicyID

    ,pp.sInsuredName

    ,QU.[ClientID]

    ,QU.[ContractProfileID]

    ,QU.[CcovSID]

    ,QU.[CoverageCode]

    ,zp.ZIPcd

    ,QU.[PPPInsuranceMethodCode]

    ,QU.[EquipmentCode]

    ,QU.[CoverageEffectiveDate]

    ,QU.LeaseEndDate

    ,QU.[CoverageExpirationDate]

    ,QU.CancellationDate

    ,QU.[LeaseEffectiveDate]

    ,QU.[OriginalEndDate]

    ,QU.[FullTermCalc]

    ,QU.[ITDEarnedPremium]

    ,QU.MonthEndDate

    ,CLMSUMF.ClaimAmt

    ,QU.MONTH_END_DATE

    ,QCAA.COAP_APPLY_AMT

    FROM

    (SELECT

    qmr.[LeasSID]

            ,[LeaseCode]

            ,[PolicyCode]

            ,[ClientID]

            ,[ContractProfileID]

            ,[CcovSID]

            ,[CoverageCode]

            ,[PPPInsuranceMethodCode]

            ,[EquipmentCode]

            ,[CoverageEffectiveDate]

            ,[CoverageExpirationDate]

            ,qccd.[CCOV_CANCELLATION_DATE] AS CancellationDate

            ,[LeaseEffectiveDate]

            ,[LeaseExpirationDate] as [OriginalEndDate]

            ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate] ELSE qccd.[CCOV_CANCELLATION_DATE] END as [LeaseEndDate]

            ,[MonthEndDate]

            ,ftc.COMP_FULL_TERM_CALC_AMT AS [FullTermCalc]

            ,[ITDEarnedPremium]

            ,ftc.COMP_SID

            ,ftc.PTRN_SID

            ,ftc.PTRN_LPRG_SID

            ,ftc.MONTH_END_DATE

      FROM [dbo].[StgQIPSMonthlyRevenue] qmr

    join [dbo].[ExtQIPSClientCoverageDetail] QCCD on qmr.CcovSID = QCCD.CCOV_SID

    join (SELECT [LeasSID],max([MonthEndDate]) as maxreportdate

          FROM [dbo].[StgQIPSMonthlyRevenue]

          group by LeasSID) QMrs on qmr.LeasSID = QMrs.LeasSID

                             and qmr.MonthEndDate = QMrs.maxreportdate

    JOIN

    ( SELECT [COMP_CCOV_SID]

      ,COMP_SID

      ,QPT.PTRN_SID

      ,QPT.PTRN_LPRG_SID

      ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE AS MONTH_END_DATE

      ,[COMP_FULL_TERM_CALC_AMT]

      ,[FCGT_FUNDED_FLAG]

      FROM [dbo].[ExtQIPSCoverageCalcComps] QCCC

    join dbo.ExtQIPSClientCoverageDetail QCCD

         on QCCC.COMP_CCOV_SID = QCCD.CCOV_SID

    left join [dbo].[ExtQIPSPaymentTransactions] QPT

         on QCCD.CCOV_LPRG_SID = QPT.PTRN_LPRG_SID

         where FCGT_FUNDED_FLAG is null      

         GROUP BY [COMP_CCOV_SID]

                  ,COMP_SID

                  ,QPT.PTRN_SID

                  ,QPT.PTRN_LPRG_SID

                  ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE

                  ,[COMP_FULL_TERM_CALC_AMT]

                  ,[FCGT_FUNDED_FLAG]

    ) ftc ON qmr.CcovSID = ftc.COMP_CCOV_SID

    group by qmr.[LeasSID]

             ,[LeaseCode]

             ,[PolicyCode]

             ,[ClientID]

             ,[ContractProfileID]

             ,[CcovSID]

             ,[CoverageCode]

             ,[PPPInsuranceMethodCode]

             ,[EquipmentCode]

             ,[CoverageEffectiveDate]

             ,[CoverageExpirationDate]
             ,qccd.[CCOV_CANCELLATION_DATE]

             ,[LeaseEffectiveDate]

             ,[LeaseExpirationDate]

             ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate] ELSE qccd.[CCOV_CANCELLATION_DATE] END

             ,[MonthEndDate]

             ,ftc.COMP_FULL_TERM_CALC_AMT

             ,[ITDEarnedPremium]

             ,ftc.COMP_SID

             ,ftc.PTRN_SID

             ,ftc.PTRN_LPRG_SID

             ,ftc.MONTH_END_DATE

           UNION ALL

    SELECT qbr.[LeasSID]

           ,[LeaseCode]

           ,[PolicyCode]

           ,[ClientID]

           ,[ContractProfileID]

           ,[CcovSID]

           ,[CoverageCode]

           ,[PPPInsuranceMethodCode]

           ,[EquipmentCode]

           ,[CoverageEffectiveDate]

           ,[CoverageExpirationDate]

           ,qccd.[CCOV_CANCELLATION_DATE] AS CancellationDate

           ,[LeaseEffectiveDate]

           ,[LeaseExpirationDate] as [OriginalEndDate]

           ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate] ELSE qccd.[CCOV_CANCELLATION_DATE] END as [LeaseEndDate]

           ,[MonthEndDate]

           ,ftc.COMP_FULL_TERM_CALC_AMT AS [FullTermCalc]

           ,[ITDEarnedPremium]

           ,ftc.COMP_SID

           ,ftc.PTRN_SID

           ,ftc.PTRN_LPRG_SID

           ,ftc.MONTH_END_DATE

    FROM [dbo].[StgQIPSBaseRevenue] qbr

    join [dbo].[ExtQIPSClientCoverageDetail] QCCD on qbr.CcovSID = QCCD.CCOV_SID

    join

    (

    SELECT

    [LeasSID],max([MonthEndDate]) as

    maxreportdate

           FROM

    [dbo].[StgQIPSBaseRevenue]

           group by

    LeasSID

    )

    QBrs on qbr.LeasSID = QBrs.

    LeasSID

                                  and

    qbr.MonthEndDate = QBrs.maxreportdate

           JOIN ( SELECT [COMP_CCOV_SID]

                         ,COMP_SID

                         ,QPT.PTRN_SID

                         ,QPT.PTRN_LPRG_SID

                         ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE AS MONTH_END_DATE

                         ,[COMP_FULL_TERM_CALC_AMT]

                         ,[FCGT_FUNDED_FLAG]

                  FROM [dbo].[ExtQIPSCoverageCalcComps] QCCC

                  join dbo.ExtQIPSClientCoverageDetail QCCD

                         on QCCC.COMP_CCOV_SID = QCCD.CCOV_SID

                  left join [dbo].[ExtQIPSPaymentTransactions] QPT

                         on QCCD.CCOV_LPRG_SID = QPT.PTRN_LPRG_SID

                         where FCGT_FUNDED_FLAG is null      

                  GROUP BY [COMP_CCOV_SID]

                           ,COMP_SID

                           ,QPT.PTRN_SID

                           ,QPT.PTRN_LPRG_SID

                           ,QPT.PYDT_INCLUDE_IN_MONTH_END_DATE

                           ,[COMP_FULL_TERM_CALC_AMT]

                           ,[FCGT_FUNDED_FLAG]) ftc ON qbr.CcovSID = ftc.COMP_CCOV_SID 

    group by qbr.[LeasSID]

             ,[LeaseCode]

             ,[PolicyCode]

             ,[ClientID]

             ,[ContractProfileID]

             ,[CcovSID]

             ,[CoverageCode]

             ,[PPPInsuranceMethodCode]

             ,[EquipmentCode]

             ,[CoverageEffectiveDate]

             ,[CoverageExpirationDate]

             ,qccd.[CCOV_CANCELLATION_DATE]

             ,[LeaseEffectiveDate]

             ,[LeaseExpirationDate]

             ,CASE WHEN qccd.[CCOV_CANCELLATION_DATE] IS NULL THEN [CoverageExpirationDate ELSE qccd.[CCOV_CANCELLATION_DATE] END
             ,[MonthEndDate]

             ,ftc.COMP_FULL_TERM_CALC_AMT

             ,[ITDEarnedPremium]  

             ,ftc.COMP_SID

             ,ftc.PTRN_SID

             ,ftc.PTRN_LPRG_SID

             ,ftc.MONTH_END_DATE) QU  

    ;WITH cte_pp AS ( SELECT *, CASE WHEN first_digit = 0 THEN '' ELSE SUBSTRING(pp.sPolicyID, first_digit, PATINDEX('%[^0-9]%',

                                                                       SUBSTRING(pp.sPolicyID + '.', first_digit, 10)) -END AS policyCode

                      FROM dbo.ExtPRSDEPolicy pp

    CROSS APPLY (SELECT PATINDEX('%[0-9]%', pp.sPolicyID) AS first_digit) AS alias1)

    SELECT * FROM QU
    IN
    NER JOIN cte_pp ctepp ON ctepp.policyCode = QU.policyCode   

    LEFT JOIN ( SELECT la.LADD_LEAS_SID

                       ,CASE WHEN Za.ZIPa IS NULL THEN Zb.ZIPb ELSE Za.ZIPa END ZIPcd

                FROM [dbo].[ExtQIPSLeaseAddresses] LA

                LEFT JOIN (SELECT LADD_LEAS_SID,
                          
    [LADD_POSTAL_CODE_TXT] AS ZIPa 
                           
    FROM [dbo].[ExtQIPSLeaseAddresses]
                          
    WHERE [LADD_ADDR_CODE]='E'

                           GROUP BY LADD_LEAS_SID,[LADD_POSTAL_CODE_TXT]) Za
               
    ON Za.LADD_LEAS_SID = LA.LADD_LEAS_SID

    LEFT JOIN (SELECT LADD_LEAS_SID,
              
    [LADD_POSTAL_CODE_TXT] AS ZIPb
             
    FROM [dbo].[ExtQIPSLeaseAddresses]
              
    WHERE [LADD_ADDR_CODE]='B'

               GROUP BY LADD_LEAS_SID,[LADD_POSTAL_CODE_TXT]) Zb
     
    ON Zb.LADD_LEAS_SID = LA.LADD_LEAS_SID

    GROUP BY  la.LADD_LEAS_SID

              ,CASE WHEN Za.ZIPa is null THEN Zb.ZIPb ELSE Za.ZIPa END) zp
    ON QU.[LeasSID] = zp.LADD_LEAS_SID
    LEFT OUTER JOIN (SELECT CLMSUM.CLAM_CCOV_SID,

                     SUM(CLMSUM.CLAIMAMT) AS ClaimAmt

                     FROM (SELECT [CLAM_CCOV_SID]
                                  ,[CLAM_PAC_CLAIMID]

                                  ,clm.CLAIMAMT

                           FROM [dbo].[ExtQIPSClaims] QC

                           join (SELECT [ClaimID]

                                        ,SUM([Amount]) AS CLAIMAMT

                                 FROM [dbo].[ExtPACTransaction]

                                 GROUP BY [ClaimID]) clm ON QC.[CLAM_PAC_CLAIMID] = clm.ClaimID) CLMSUM
                           G
    ROUP BY CLMSUM.CLAM_CCOV_SID) CLMSUMF ON QU.[CcovSID] = CLMSUMF.CLAM_CCOV_SID

                left join [dbo].[ExtQIPSCashApplyAmounts] QCAA on Qu.PTRN_LPRG_SID = QCAA.LPCA_LPRG_SID
                                                              
    and qu.COMP_SID = QCAA.COAP_COMP_SID

                                                              and Qu.PTRN_SID = QCAA.LPCA_PTRN_SID


                where year([CoverageEffectiveDate]) like '2001%' 
                     
    AND sPolicyID = '1035445'  

                order by LeasSID, sPolicyID



























































































































































































  • daniness - Thursday, July 12, 2018 12:37 PM

    Thanks for all of your input, folks.

    @scottpletcher , I'm able to somewhat understand your solution, but could you please advise how I could fit it into this code? Would this work, and I apologize for the monstrosity this is. I'm not even sure how the BA was able to wrap his head around this query...I have great difficulty comprehending such ridiculously long and convoluted ones:

    My code looks complicated, but all it does is find the first numeric char, 0 thru 9 (a digit), in the string, and starting with that digit, take all digits after that one until one is not a digit.  That is, it gets the first contiguous/continuous string of digits.  For example, for this string:
    AB1234C56
    it would get '1234', because C is not a digit, ending the "chain".  Because the string could end before a non-digit, I add a . to the string so that there's always a non-numeric char to end the pull.  For example, if the original string was:
    AB12345
    the string I check would be:
    AB12345.
    And the dot would end the contiguous digits, so I'd return 12345 (as you'd want from the a column containing "AB12345").

    As to incorporating into another query, by far the easiest way would be to do another CROSS APPLY to derive the final extracted policyCode number and give it a usable name in the query.


    SELECT *,
    FROM dbo.t2 t2 /* the table containing the column with the embedded policyCode number */ 
    CROSS APPLY (
        SELECT PATINDEX('%[0-9]%', t2.PolicyID) AS first_digit
    ) AS alias1
    CROSS APPLY (
        SELECT CASE WHEN first_digit = 0 THEN '' ELSE SUBSTRING(t2.policyID, first_digit, 
            PATINDEX('%[^0-9]%', SUBSTRING(t2.policyID + '.', first_digit, 10)) - 1) 
            END AS policyCode
    ) AS alias2

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thanks, @scottpletcher. I appreciate the clarification...I'll see if I can try implementing this into the solution. 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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