USP_DATALIST_KPIINSTANCEVALUEHISTORY

Displays the value history for the given KPI.

Parameters

Parameter	Parameter Type	Mode	Description
@KPIINSTANCEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STARTDATE	datetime	IN	Start
@ENDDATE	datetime	IN	End

Definition

Copy



CREATE procedure dbo.USP_DATALIST_KPIINSTANCEVALUEHISTORY
(
    @KPIINSTANCEID uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
as
    set nocount on;

    if (@STARTDATE is not null) and (@ENDDATE is not null)
        select 
            ASOFDATE, 
            VALUE
        from dbo.KPIINSTANCEHISTORY
        where KPIINSTANCEHISTORY.KPIINSTANCEID = @KPIINSTANCEID
              and KPIINSTANCEHISTORY.ASOFDATE between dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
        order by KPIINSTANCEHISTORY.ASOFDATE desc;

    else if @STARTDATE is not null
        select 
            ASOFDATE, 
            VALUE
        from dbo.KPIINSTANCEHISTORY
        where KPIINSTANCEHISTORY.KPIINSTANCEID = @KPIINSTANCEID
              and KPIINSTANCEHISTORY.ASOFDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)
        order by KPIINSTANCEHISTORY.ASOFDATE desc;

    else if @ENDDATE is not null
        select 
            ASOFDATE, 
            VALUE
        from dbo.KPIINSTANCEHISTORY
        where KPIINSTANCEHISTORY.KPIINSTANCEID = @KPIINSTANCEID
              and KPIINSTANCEHISTORY.ASOFDATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
        order by KPIINSTANCEHISTORY.ASOFDATE desc;

    else
        select 
            ASOFDATE, 
            VALUE
        from dbo.KPIINSTANCEHISTORY
        where KPIINSTANCEHISTORY.KPIINSTANCEID = @KPIINSTANCEID
        order by KPIINSTANCEHISTORY.ASOFDATE desc;