case when then min()

ktecyv1j 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(525)

协助案件陈述的第二个阶段。我需要为下面的场景编码：如果有两个pcc=null的记录，那么选择具有最小值的providerfinaltier。它当前正在选择表的min providerfinaltier。

<!-- language: lang-sql-->
UPDATE spc
SET ProviderTier = CASE WHEN (coalesce(spc.rulespecialtyid, spc.RenderingproviderspecialtyID)  = mft.SpecialtyID and coalesce(spc.RulePCC,spc.PCC) = mft.PCC ) THEN ProviderFinalTier
    WHEN  (coalesce(spc.rulespecialtyid, spc.RenderingproviderspecialtyID) = mft.SpecialtyID and mft.pcc is null) 
    THEN (Select min(ProviderFinalTier) from Common.medicarefinaltiers where coalesce(spc.rulespecialtyid, spc.RenderingproviderspecialtyID) = mft.SpecialtyID and mft.pcc is null)
    ELSE 2 END
from analysis.AnalysisMatchedClaims spc
left join Compass_REPORTING.dbo.Payer p on p.Payer_Key = spc.PayerKey
left join Common.medicarefinaltiers mft on /*mft.EmployerId = spc.EmployerId
                                  and */mft.ProviderID = spc.RenderingProviderId
                                  and mft.SpecialtyId = coalesce(spc.rulespecialtyid, spc.RenderingproviderspecialtyID)  
                                  and mft.PayerGroup = p.Payer_Group
WHERE spc.ProviderTier is null
  and (mft.pcc = coalesce(spc.RulePCC,spc.PCC) or mft.PCC is null)
  and spc.RuleClaimType = 'I'

sql sql-server case-when Min

来源：https://stackoverflow.com/questions/63307485/case-when-then-min

1条答案

按热度按时间

xzv2uavs1#

在此子查询中：

Select min(ProviderFinalTier) 
from Common.medicarefinaltiers 
where coalesce(spc.rulespecialtyid, spc.RenderingproviderspecialtyID) = mft.SpecialtyID 
  and mft.pcc is null

你没有给表加别名 Common.medicarefinaltiers 有没有提到 mft 实际上是对外部联接表的引用 Common.medicarefinaltiers .
我认为应该为表提供一个别名，并在以下条件下使用该别名：

Select min(m.ProviderFinalTier) 
from Common.medicarefinaltiers m 
where coalesce(spc.rulespecialtyid, spc.RenderingproviderspecialtyID) = m.SpecialtyID 
  and m.pcc is null

赞(0）回复(0）举报 2021-07-24

我来回答

case when then min()

1条答案

相关问题

热门标签

最新问答