USP_KPIINSTANCEHISTORY_ADD

Creates a historical record of the value of a KPI instance on a specified date.

Parameters

Parameter	Parameter Type	Mode
@KPIINSTANCEID	uniqueidentifier	IN
@VALUE	decimal(20, 5)	IN
@ASOFDATE	datetime	IN
@NUMBEROFITEMSTOSTORE	int	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_KPIINSTANCEHISTORY_ADD(
    @KPIINSTANCEID uniqueidentifier,
    @VALUE decimal(20, 5),
    @ASOFDATE datetime,
    @NUMBEROFITEMSTOSTORE int,
    @CHANGEAGENTID uniqueidentifier)
as
    set nocount on;

  declare @ID uniqueidentifier;
  set @ID = newID();

    --Insert the new history item.

    insert into dbo.KPIINSTANCEHISTORY (ID, KPIINSTANCEID,
        VALUE,
        ASOFDATE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED)
    values (@ID, @KPIINSTANCEID,
        @VALUE,
        @ASOFDATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        getdate(),
        getdate());

  exec dbo.USP_KPIINSTANCESTATUSHISTORY_EVALUATE @ID, @KPIINSTANCEID, @VALUE, @ASOFDATE, @CHANGEAGENTID

    if @NUMBEROFITEMSTOSTORE > 0
    begin
        --Delete old history items.

        delete from dbo.KPIINSTANCEHISTORY 
        where KPIINSTANCEID = @KPIINSTANCEID 
            and ID not in (select top(@NUMBEROFITEMSTOSTORE) ID 
                from dbo.KPIINSTANCEHISTORY 
                where KPIINSTANCEID = @KPIINSTANCEID 
                order by ASOFDATE desc);
    end;