Altering SQL Stored procedure to have max result be 100-CodePudding

I am attempting to manipulate the results of a stored procedure to not expel a result greater than 100.

Essentially if the result is >=100 I want my result to be 100.

Here is what I currently have

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

if a result is greater than 100, I want it to not surpass that 100 mark.

anything >=100 must be = 100

CodePudding user response：

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

If it's the SLPercentage field you're looking to display conditionally I would wrap the whole statement in virtual table like this. Otherwise you will have to express the SLPrecentage formula twice in the conditional statement. Makes things hard to follow, IMO.

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

CodePudding user response：

I think what you're referring to in your question is the value of SLPercentage? It's not clear.

If so, you need to use a case expression which you can either do by repeating the aggregate expression, or another way which is to perform the aggregation in an apply. The following is naturally untested however should be close to what you need, if I'm interpereting your question correctly and assuming you're using SQL-Server.

  select c.ServiceName, c.ServiceRow, c.ServicePercentage, 
    isnull(case when b.SLPercentage > 100 then 100 else SLPercentage end, 0) SLPercentage, 
    isnull(b.DatetimeStamp, ' ') DatetimeStamp
  from CS5_QueueGroups2 c
  outer apply (
    select round(sum(isNull(b.SLAMet 0.0,0.0))/sum(isNull(b.AnsweredCalls 0.0,0.0))*100.,0) SLPercentage,
           max(b.DateTimeStamp) DateTimeStamp
    from CS750_1 b 
    where b.QueueName = c.QueueName
  )b
  where c.ServiceMonitor = @Wallboard
  order by c.ServiceRow;