USP_DATALIST_KPIINSTANCESTATUSHISTORY

Lists any kpi instance where the status changes from one threshold value to another.

Parameters

Parameter	Parameter Type	Mode	Description
@KPIINSTANCEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DATAWINDOWOPEN	datetime	IN	Data window open
@DATAWINDOWCLOSE	datetime	IN	Data window close
@PREVIOUSSTATUSCODE	tinyint	IN
@CURRENTSTATUSCODE	tinyint	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_KPIINSTANCESTATUSHISTORY
(
    @KPIINSTANCEID uniqueidentifier,  
    @DATAWINDOWOPEN datetime = null,
    @DATAWINDOWCLOSE datetime = null,
    @PREVIOUSSTATUSCODE tinyint = 255,
    @CURRENTSTATUSCODE tinyint = 255
)
as
    set nocount on;

    set @PREVIOUSSTATUSCODE = coalesce(@PREVIOUSSTATUSCODE, 255);
    set @CURRENTSTATUSCODE = coalesce(@CURRENTSTATUSCODE, 255);

    select 
        KPIINSTANCESTATUSHISTORY.ID KPIINSTANCESTATUSHISTORYID,
        KPIINSTANCE.ID KPIINSTANCEID,
        KPIINSTANCE.NAME,
        KPIINSTANCESTATUSHISTORY.CURRENTVALUE,
        dbo.UFN_KPIINSTANCEGOAL_GETIMAGEKEY(KPIINSTANCESTATUSHISTORY.CURRENTVALUE, KPIINSTANCESTATUSHISTORY.GOALAIMTYPECODE, KPIINSTANCESTATUSHISTORY.GOALUPWARDLOWVALUE, KPIINSTANCESTATUSHISTORY.GOALUPWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDHIGHVALUE) CURRENTVALUEIMAGEKEY,
        KPIINSTANCESTATUSHISTORY.CURRENTSTATUS,
        KPIINSTANCESTATUSHISTORY.PREVIOUSVALUE,
        dbo.UFN_KPIINSTANCEGOAL_GETIMAGEKEY(KPIINSTANCESTATUSHISTORY.PREVIOUSVALUE, KPIINSTANCESTATUSHISTORY.GOALAIMTYPECODE, KPIINSTANCESTATUSHISTORY.GOALUPWARDLOWVALUE, KPIINSTANCESTATUSHISTORY.GOALUPWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDHIGHVALUE) PREVIOUSVALUEIMAGEKEY,
        KPIINSTANCESTATUSHISTORY.PREVIOUSSTATUS,        
        Cast(KPIINSTANCESTATUSHISTORY.ASOFDATE as datetime) ASOFDATE,
        dbo.UFN_KPIINSTANCEGOAL_GETYELLOWTARGET(KPIINSTANCESTATUSHISTORY.GOALAIMTYPECODE, KPIINSTANCESTATUSHISTORY.GOALUPWARDLOWVALUE, KPIINSTANCESTATUSHISTORY.GOALUPWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDHIGHVALUE) YELLOWTARGET,
        dbo.UFN_KPIINSTANCEGOAL_GETGREENTARGET(KPIINSTANCESTATUSHISTORY.GOALAIMTYPECODE, KPIINSTANCESTATUSHISTORY.GOALUPWARDLOWVALUE, KPIINSTANCESTATUSHISTORY.GOALUPWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDMIDVALUE, KPIINSTANCESTATUSHISTORY.GOALDOWNWARDHIGHVALUE) GREENTARGET,
        'a3d45719-d44f-40dd-9db7-25f23cfe8f17' PAGEID        
    from dbo.KPIINSTANCESTATUSHISTORY
        inner join dbo.KPIINSTANCE on KPIINSTANCESTATUSHISTORY.KPIINSTANCEID = KPIINSTANCE.ID
    where (KPIINSTANCESTATUSHISTORY.KPIINSTANCEID = @KPIINSTANCEID) and
          ((@DATAWINDOWOPEN is null) or (KPIINSTANCESTATUSHISTORY.DATEADDED >= @DATAWINDOWOPEN)) and 
          ((@DATAWINDOWCLOSE is null) or (KPIINSTANCESTATUSHISTORY.DATEADDED < @DATAWINDOWCLOSE)) and
          ((@PREVIOUSSTATUSCODE = 255) or (PREVIOUSSTATUSCODE = @PREVIOUSSTATUSCODE)) and
          ((@CURRENTSTATUSCODE = 255) or (CURRENTSTATUSCODE = @CURRENTSTATUSCODE))
     order by KPIINSTANCESTATUSHISTORY.DATEADDED DESC