修改SQL存储过程限制SLPercentage结果最大值为100

要实现SLPercentage结果≥100时显示为100，只需在原计算逻辑外层添加条件判断，用IIF或CASE WHEN函数取计算值和100中的较小值。以下是修改后的完整存储过程代码：

ALTER PROCEDURE [dbo].[USP_5_ServiceLevel]
    @Wallboard int 
AS
BEGIN
    SET NOCOUNT ON; 
    
    select a.ServiceName, a.ServiceRow, a.ServicePercentage, 
           -- 添加IIF判断，限制SLPercentage不超过100
           IIF(isnull(round(sum(isNull(b.SLAMet+0.0,0.0))/sum(isNull(b.AnsweredCalls+0.0,0.0))*100.,0),0) >= 100, 100, 
               isnull(round(sum(isNull(b.SLAMet+0.0,0.0))/sum(isNull(b.AnsweredCalls+0.0,0.0))*100.,0),0)) SLPercentage,
           isNull(max(b.DateTimeStamp),' ') DateTimeStamp
    from CS5_QueueGroups2 A
    left outer join CS750_1 b on a.QueueName = b.QueueName
    where a.ServiceMonitor = @Wallboard
    group by a.ServiceName, a.ServiceRow, a.ServicePercentage
    order by a.ServiceRow
    
END

关键修改说明 #

原SLPercentage的计算结果被包裹在IIF函数中：

• 当计算值≥100时，返回100
• 否则返回原计算的百分比值

如果需要兼容SQL Server 2012之前的版本（IIF是2012引入的），可以改用CASE WHEN写法：

CASE 
    WHEN isnull(round(sum(isNull(b.SLAMet+0.0,0.0))/sum(isNull(b.AnsweredCalls+0.0,0.0))*100.,0),0) >= 100 THEN 100
    ELSE isnull(round(sum(isNull(b.SLAMet+0.0,0.0))/sum(isNull(b.AnsweredCalls+0.0,0.0))*100.,0),0)
END SLPercentage

内容的提问来源于stack exchange，提问作者KeepVBSalive