USP_SEARCH_KPIINSTANCE

CREATE procedure dbo.USP_SEARCH_KPIINSTANCE(
    @NAME nvarchar(255) = null, 
    @MAXROWS smallint,
    @CATEGORY nvarchar(200) = null,
    @TYPEID uniqueidentifier = null,
    @GOALSTATUS int = null
)
as
    set nocount on;

    set @NAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@NAME, 0, null);
    set @CATEGORY = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@CATEGORY, 0, null);

    select top(@MAXROWS) 
        KPIINSTANCE.ID,
        KPIINSTANCE.NAME,
        KPICATALOG.NAME KPITYPE
    from dbo.KPIINSTANCE
        left join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
        outer apply (
            select top (1) [VALUE]
            from dbo.[KPIINSTANCEHISTORY]
            where [KPIINSTANCEID] = [KPIINSTANCE].[ID]
            order by [ASOFDATE] desc
        ) as [HISTORY]
    where 
        (@NAME is null 
            or KPIINSTANCE.NAME like @NAME
        )
        and(@CATEGORY is null
            or KPICATALOG.UIFOLDER like @CATEGORY
        )
        and(@TYPEID is null
            or KPICATALOG.ID = @TYPEID
        )
        and(@GOALSTATUS is null
            or(
                @GOALSTATUS = 0 --Red
                and(
                    ([KPIINSTANCE].[GOALAIMTYPECODE] = 0 --Up
                        and [HISTORY].[VALUE] < [KPIINSTANCE].[GOALUPWARDLOWVALUE]
                    )
                    or([KPIINSTANCE].[GOALAIMTYPECODE] = 1 --Down
                        and [HISTORY].[VALUE] > [KPIINSTANCE].[GOALDOWNWARDHIGHVALUE]
                    )
                )
            )
            or(
                @GOALSTATUS = 1 --Yellow
                and(
                    ([KPIINSTANCE].[GOALAIMTYPECODE] = 0 --Up
                        and [HISTORY].[VALUE] >= [KPIINSTANCE].[GOALUPWARDLOWVALUE]
                        and [HISTORY].[VALUE] < [KPIINSTANCE].[GOALUPWARDMIDVALUE]
                    )
                    or([KPIINSTANCE].[GOALAIMTYPECODE] = 1 --Down
                        and [HISTORY].[VALUE] <= [KPIINSTANCE].[GOALDOWNWARDHIGHVALUE]
                        and [HISTORY].[VALUE] > [KPIINSTANCE].[GOALDOWNWARDMIDVALUE]
                    )
                )
            )
            or(
                @GOALSTATUS = 2 --Green
                and(
                    ([KPIINSTANCE].[GOALAIMTYPECODE] = 0 --Up
                        and [HISTORY].[VALUE] >= [KPIINSTANCE].[GOALUPWARDMIDVALUE]
                    )
                    or([KPIINSTANCE].[GOALAIMTYPECODE] = 1 --Down
                        and [HISTORY].[VALUE] <= [KPIINSTANCE].[GOALDOWNWARDMIDVALUE]
                    )
                )
            )
        )
    order by KPIINSTANCE.NAME;