USP_KPI_BATCHSTATISTICSCOUNT

CREATE procedure dbo.USP_KPI_BATCHSTATISTICSCOUNT
    @APPUSERID uniqueidentifier,
    @BATCHTYPECATALOGID uniqueidentifier,
    @TRANSACTIONTYPE int,
    @ASOFDATE datetime = null, 
    @STARTDATE datetime = null,
    @VALUE int = 0 output

    with execute as owner as    

    set nocount on;                       

    if @ASOFDATE is null set @ASOFDATE=getdate();
    set @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

    if @TRANSACTIONTYPE = 1
        select 
            @VALUE = sum(NUMBERROWSADDED) 
        from dbo.BATCHSTATISTICS 
        where
            (DATEADDED >= @STARTDATE or @STARTDATE is null) and
            (DATEADDED <= @ASOFDATE or @ASOFDATE is null) and
            APPUSERID = @APPUSERID and
            BATCHTYPECATALOGID = @BATCHTYPECATALOGID;
    else if @TRANSACTIONTYPE = 2
        select 
            @VALUE = sum(NUMBERROWSCHANGED)
        from dbo.BATCHSTATISTICS 
        where
            (DATEADDED >= @STARTDATE or @STARTDATE is null) and
            (DATEADDED <= @ASOFDATE or @ASOFDATE is null) and
            APPUSERID = @APPUSERID and
            BATCHTYPECATALOGID = @BATCHTYPECATALOGID;                        
    else if @TRANSACTIONTYPE = 3
        select 
            @VALUE = sum(NUMBERROWSDELETED) 
        from dbo.BATCHSTATISTICS 
        where
            (DATEADDED >= @STARTDATE or @STARTDATE is null) and
            (DATEADDED <= @ASOFDATE or @ASOFDATE is null) and
            APPUSERID = @APPUSERID and
            BATCHTYPECATALOGID = @BATCHTYPECATALOGID;                             
    else if @TRANSACTIONTYPE = 4
        select 
            @VALUE = sum(NUMBERROWSCOMMITTEDSUCCESSFULLY)
        from dbo.BATCHSTATISTICS 
        where
            (DATEADDED >= @STARTDATE or @STARTDATE is null) and
            (DATEADDED <= @ASOFDATE or @ASOFDATE is null) and
            APPUSERID = @APPUSERID and
            BATCHTYPECATALOGID = @BATCHTYPECATALOGID;                            
    else if @TRANSACTIONTYPE = 5
        select 
            @VALUE = sum(NUMBERROWEXCEPTIONS) 
        from dbo.BATCHSTATISTICS 
        where
            (DATEADDED >= @STARTDATE or @STARTDATE is null) and
            (DATEADDED <= @ASOFDATE or @ASOFDATE is null) and
            APPUSERID = @APPUSERID and
            BATCHTYPECATALOGID = @BATCHTYPECATALOGID;

if @VALUE is null set @VALUE=0;