UFN_KPIINSTANCEVALUE_GETSTATUSCODE

Returns status value (0 - red, 1 - yellow, 2 - green) for a given KPI value based on its goal aim type and threshold values.

Return

Return Type
tinyint

Parameters

Parameter	Parameter Type	Mode
@VALUE	decimal(20, 5)	IN
@GOALAIMTYPECODE	tinyint	IN
@GOALUPWARDLOWVALUE	decimal(20, 5)	IN
@GOALUPWARDMIDVALUE	decimal(20, 5)	IN
@GOALDOWNWARDMIDVALUE	decimal(20, 5)	IN
@GOALDOWNWARDHIGHVALUE	decimal(20, 5)	IN

Definition

Copy



CREATE function dbo.UFN_KPIINSTANCEVALUE_GETSTATUSCODE(
  @VALUE decimal(20,5),
  @GOALAIMTYPECODE tinyint,
  @GOALUPWARDLOWVALUE decimal(20,5),
  @GOALUPWARDMIDVALUE decimal(20,5),
  @GOALDOWNWARDMIDVALUE decimal(20,5), 
  @GOALDOWNWARDHIGHVALUE decimal(20,5)  
  )
returns tinyint
with execute as caller
as begin

declare @GOALSTATUSCODE tinyint;

if @GOALAIMTYPECODE = 0 
      begin
        if @VALUE < @GOALUPWARDLOWVALUE
          --red

          set @GOALSTATUSCODE = 0
        else
          if @VALUE < @GOALUPWARDMIDVALUE
            --yellow

            set @GOALSTATUSCODE = 1
          else
            --green

            set @GOALSTATUSCODE = 2
      end
    else
      begin
        if @VALUE > @GOALDOWNWARDHIGHVALUE
          --red

          set @GOALSTATUSCODE = 0
        else
          if @VALUE > @GOALDOWNWARDMIDVALUE
            --yellow

            set @GOALSTATUSCODE = 1
          else
            --green

            set @GOALSTATUSCODE = 2
      end

 return @GOALSTATUSCODE;
end