USP_KPIINSTANCE_GETGOALSTATUS

Gets a list of values for a given KPI instance (or all instances if no ID is supplied).
Parameters

Parameter	Parameter Type	Mode
@KPIINSTANCEID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@MAXROWS	int	IN
Definition

Copy


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

                select top(@MAXROWS) 
                    KPIINSTANCE.ID KPIINSTANCEID,
                    KPIINSTANCE.KPICATALOGID,
                    KPIINSTANCE.NAME,
                    KPICATALOG.GOALTYPECODE,
                    KPICATALOG.GOALTYPE,
                    KPIINSTANCE.GOALINDICATORTYPECODE,
                    KPIINSTANCE.GOALAIMTYPECODE,
                    KPIINSTANCE.GOALVALUE,
                    KPIINSTANCE.GOALUPWARDLOWVALUE,
                    KPIINSTANCE.GOALUPWARDMIDVALUE,
                    KPIINSTANCE.GOALDOWNWARDMIDVALUE,
                    KPIINSTANCE.GOALDOWNWARDHIGHVALUE,
                    KPIINSTANCEHISTORY.VALUE KPIVALUE,
                    case when KPIINSTANCEHISTORY.VALUE is null then convert(bit, 0)
                        else convert(bit, 1) end HASVALUE,
                    KPIINSTANCEHISTORY.ASOFDATE CLOSESTASOFDATE,
                    KPICATALOG.GOALRETRIEVALTYPECODE,
                    KPIINSTANCE.PARAMETERSXML,
                    KPICATALOG.DECIMALPLACES
                from dbo.KPIINSTANCEHISTORY
                inner join dbo.KPIINSTANCE on KPIINSTANCEHISTORY.KPIINSTANCEID = KPIINSTANCE.ID
                inner join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
                where (@KPIINSTANCEID is null or KPIINSTANCEHISTORY.KPIINSTANCEID = @KPIINSTANCEID)
                    and KPIINSTANCEHISTORY.ASOFDATE between dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)
                order by KPIINSTANCEHISTORY.ASOFDATE desc;