July 11, 2018 at 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, 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
July 11, 2018 at 3:03 pm
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.
July 11, 2018 at 3:21 pm
daniness - Wednesday, July 11, 2018 2:31 PMHi 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)
July 11, 2018 at 3:40 pm
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.
-- Itzik Ben-Gan 2001
July 12, 2018 at 10:25 am
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.
July 12, 2018 at 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:
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)) -1 END AS policyCode
FROM dbo.ExtPRSDEPolicy pp
CROSS APPLY (SELECT PATINDEX('%[0-9]%', pp.sPolicyID) AS first_digit) AS alias1)
SELECT * FROM QU
INNER 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
GROUP 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)) -1 END AS policyCode
FROM dbo.ExtPRSDEPolicy pp
CROSS APPLY (SELECT PATINDEX('%[0-9]%', pp.sPolicyID) AS first_digit) AS alias1)
SELECT * FROM QU
INNER 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
GROUP 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
July 12, 2018 at 12:53 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.
July 17, 2018 at 2:28 pm
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