USP_KPIINSTANCESTATUSHISTORY_EVALUATE

Evaluates whether or not the KPI status changed and if so stores an entry in the KPI Instance Status History table.

Parameters

Parameter	Parameter Type	Mode
@KPIINSTANCEHISTORYID	uniqueidentifier	IN
@KPIINSTANCEID	uniqueidentifier	IN
@VALUE	decimal(20, 5)	IN
@ASOFDATE	datetimeoffset	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_KPIINSTANCESTATUSHISTORY_EVALUATE(
  @KPIINSTANCEHISTORYID uniqueidentifier,
  @KPIINSTANCEID uniqueidentifier, 
  @VALUE decimal(20,5),   
  @ASOFDATE datetimeoffset,
  @CHANGEAGENTID uniqueidentifier)
as
begin

  declare @PREVIOUSVALUE decimal(20,5)
  declare @GOALAIMTYPECODE tinyint
  declare @GOALUPWARDLOWVALUE decimal(20,5)
  declare @GOALUPWARDMIDVALUE decimal(20,5)
  declare @GOALDOWNWARDMIDVALUE decimal(20,5)
  declare @GOALDOWNWARDHIGHVALUE decimal(20,5)

  --get the last value calculated for this KPI

  select 
      @GOALAIMTYPECODE=GOALAIMTYPECODE, 
    @GOALUPWARDLOWVALUE=GOALUPWARDLOWVALUE,
    @GOALUPWARDMIDVALUE=GOALUPWARDMIDVALUE,
    @GOALDOWNWARDMIDVALUE=GOALDOWNWARDMIDVALUE,
    @GOALDOWNWARDHIGHVALUE=GOALDOWNWARDHIGHVALUE,
    @PREVIOUSVALUE=isnull((select top 1 KPIINSTANCEHISTORY.VALUE from dbo.KPIINSTANCEHISTORY where KPIINSTANCE.ID = KPIINSTANCEHISTORY.KPIINSTANCEID and KPIINSTANCEHISTORY.ID <> @KPIINSTANCEHISTORYID and KPIINSTANCEHISTORY.ASOFDATE < @ASOFDATE order by ASOFDATE desc),0)
  from 
      dbo.KPIINSTANCE 
  where 
    KPIINSTANCE.ID = @KPIINSTANCEID 

    if @PREVIOUSVALUE is null 
      set @PREVIOUSVALUE = 0      

    declare @PREVIOUSSTATUS tinyint
    declare @CURRENTSTATUS tinyint

    select @PREVIOUSSTATUS = dbo.UFN_KPIINSTANCEVALUE_GETSTATUSCODE(@PREVIOUSVALUE, @GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE)
    select @CURRENTSTATUS = dbo.UFN_KPIINSTANCEVALUE_GETSTATUSCODE(@VALUE, @GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE)

    if @PREVIOUSSTATUS <> @CURRENTSTATUS
      begin
          if @CHANGEAGENTID is null
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

          declare @DATE datetime
          set @DATE = GetDate();

          insert into dbo.KPIINSTANCESTATUSHISTORY (KPIINSTANCEID, ASOFDATE, CURRENTVALUE, PREVIOUSVALUE, CURRENTSTATUSCODE, PREVIOUSSTATUSCODE, GOALAIMTYPECODE, GOALUPWARDLOWVALUE, GOALUPWARDMIDVALUE, GOALDOWNWARDMIDVALUE, GOALDOWNWARDHIGHVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@KPIINSTANCEID, @ASOFDATE, @VALUE, @PREVIOUSVALUE, @CURRENTSTATUS, @PREVIOUSSTATUS, @GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE)
      end

end