好的-我有一个带有索赔ID和混合procedureCodes的表-我需要做的是隔离那些只存在以8开头的过程代码的索赔ID-因此在下面,我需要能够仅获取那些具有索赔ID的记录,这些记录的claimID类似于'8%‘代码存在-没有其他procedureCodes等-所以从下面只有2和3个claimID
claimID procedureCode
1 85025
1 97110
2 85025
2 80102
3 87112
3 81020如果报销申请有任何以不同数字开头的记录,我希望排除claimID。
发布于 2015-04-01 00:57:27
SELECT DISTINCT yt1.ClaimID
FROM YourTable yt1
WHERE yt1.procedureCode LIKE '8%'
AND NOT EXISTS (SELECT 1
FROM YourTable yt2
WHERE yt2.ClaimID = yt1.ClaimID
AND yt2.procedureCode NOT LIKE '8%');发布于 2015-04-01 00:54:39
我们分三步来做这件事。第一步是找出不符合"No other digit“要求的记录。第二步是找到符合“必须从8开始”要求的记录。第三步是使用exclusion join将两个集合匹配在一起,这样我们就可以从步骤2中获取集合,并从步骤1中排除该集合:
我们还需要知道procedureCode列的数据类型。对CPT codes略知一二,我希望char(5)或类似的代码是最合适的选择,这取决于您如何处理修饰符。如果这是你存储它的方式,那么这应该是可行的:
With BadClaims As ( --Step 1
SELECT distinct ClaimID
FROM [Table]
WHERE procedureCode NOT LIKE '8%'
), GoodClaims AS ( --Step 2
SELECT distinct ClaimID
FROM [Table]
WHERE procedureCode LIKE '8%'
)
SELECT g.ClaimID --Step 3
FROM GoodClaims g
LEFT JOIN BadClaims b on b.ClaimID = g.ClaimID
WHERE b.ClaimID IS NULL但是,您可能已将其存储为整数/数值字段。如果是这样的话,这应该是可行的:
With BadClaims As ( --Step 1
SELECT distinct ClaimID
FROM [Table]
WHERE procedureCode < 80000 or procedureCode >= 90000
), GoodClaims AS ( --Step 2
SELECT distinct ClaimID
FROM [Table]
WHERE procedureCode LIKE >= 80000 and procedureCode < 90000
)
SELECT g.ClaimID --Step 3
FROM GoodClaims g
LEFT JOIN BadClaims b on b.ClaimID = g.ClaimID
WHERE b.ClaimID IS NULL这是一个很长的形式,用来解释这里发生的事情。您还可以将这些步骤简化为单个自联接:
SELECT distinct t1.claimID
FROM [Table] t1
LEFT JOIN [Table] t2 ON t2.claimID = t1.ClaimID and t2.procecureCode NOT LIKE '8%'
WHERE t1.procedureCode LIKE '8%' and t2.claimID IS NULL https://stackoverflow.com/questions/29374100
复制相似问题