修改SQL存储过程的最大结果为100

Question

我正在尝试操作一个存储过程的结果，使其不排除大于100的结果。

如果结果是>=100，我希望我的结果是100。

这是我目前拥有的

ALTER PROCEDURE [dbo].[USP_5_ServiceLevel]
@Wallboard int 
AS
BEGIN
SET NOCOUNT ON; 

--select a.ServiceName, a.ServiceRow, a.ServicePercentage, isnull(round((sum(b.SLAMet+0.0)/sum(b.AnsweredCalls+0.0))*100.,0),0) SLPercentage,
select a.ServiceName, a.ServiceRow, a.ServicePercentage, 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

如果结果大于100，我希望它不超过100。

任何>=100必须=100

Answer 1

ALTER PROCEDURE [dbo].[USP_5_ServiceLevel]
@Wallboard int 
AS
BEGIN
SET NOCOUNT ON; 

select a.ServiceName,
a.ServiceRow, 
iif(ServicePercentage >= 100, 100, [ServicePercentage]) as [ServicePercentage],
isnull(round(sum(isNull(b.SLAMet+0.0,0.0))/sum(isNull(b.AnsweredCalls+0.0,0.0))*100.,0),0) as SLPercentage,
isNull(max(b.DateTimeStamp),' ') as 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字段，我会像这样将整个语句包装在虚拟表中。否则，您将不得不在条件语句中两次表示slpercentage公式。在我看来，这让事情变得难以理解。

select ServiceName, ServiceRow, ServicePercentage, iif(SLPercentage >= 100, 100, SLPercentage) as SLPercentage, SLPercentage, DateTimeStamp from (
select a.ServiceName,
a.ServiceRow, 
ServicePercentage,
isnull(round(sum(isNull(b.SLAMet+0.0,0.0))/sum(isNull(b.AnsweredCalls+0.0,0.0))*100.,0),0) as SLPercentage,
isNull(max(b.DateTimeStamp),' ') as 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) 
as t1