时间戳

首页 > 解决方案 > 情况下 min()

sql - 情况下 min()

问题描述

协助 CASE 语句的第二个 WHEN。我需要为以下场景编写代码：如果有两条 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'

标签： sqlsql-servermincase-when

解决方案

在这个子查询中：

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

推荐阅读