USP_REPORT_FEPBENCHMARKINGCOMPARISON_GETFEPDATA

create procedure dbo.USP_REPORT_FEPBENCHMARKINGCOMPARISON_GETFEPDATA
(
    @YEAR dbo.UDT_YEAR,
    @SUBSECTORCODE tinyint
)
as
    declare @NUMRECORDS as integer;
    declare @ALPHABET as nvarchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    select
        @NUMRECORDS = count(*)    
    from dbo.FEPDATA
    where SURVEYYEAR = @YEAR
        and (
            @SUBSECTORCODE = 0
            or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
        );

    if @NUMRECORDS > 0 
        select
            sum(coalesce(NUMBER_NEW_2, 0)) / @NUMRECORDS as NUMDONORS_NEW,
            sum(coalesce(AMOUNT_NEW_2, 0)) / @NUMRECORDS as AMOUNT_NEW,

            sum(coalesce(NUMBER_RECAPTURED_2, 0)) / @NUMRECORDS as NUMDONORS_RECAPTURED,
            sum(coalesce(AMOUNT_RECAPTURED_2, 0)) / @NUMRECORDS as AMOUNT_RECAPTURED,

            sum(coalesce(NUMBER_UPGRADED_2, 0)) / @NUMRECORDS as NUMDONORS_UPGRADED,
            sum(coalesce(AMOUNT_UPGRADED_2, 0) - coalesce(AMOUNT_UPGRADED_1, 0)) / @NUMRECORDS as AMOUNT_UPGRADED,

            sum(coalesce(NUMBER_SAME_2, 0)) / @NUMRECORDS as NUMDONORS_SAME,
            sum(coalesce(AMOUNT_SAME_2, 0)) / @NUMRECORDS as AMOUNT_SAME,

            sum(coalesce(NUMBER_DOWNGRADED_2, 0)) / @NUMRECORDS as NUMDONORS_DOWNGRADED,
            sum(coalesce(AMOUNT_DOWNGRADED_1, 0) - coalesce(AMOUNT_DOWNGRADED_2, 0)) / @NUMRECORDS as AMOUNT_DOWNGRADED,

            sum(coalesce(NUMBER_LAPSED_NEW_1, 0)) / @NUMRECORDS as NUMDONORS_LAPSEDNEW,
            sum(coalesce(AMOUNT_LAPSED_NEW_1, 0)) / @NUMRECORDS as AMOUNT_LAPSEDNEW,

            sum(coalesce(NUMBER_LAPSED_OTHER_1, 0)) / @NUMRECORDS as NUMDONORS_LAPSEDOTHER,
            sum(coalesce(AMOUNT_LAPSED_OTHER_1, 0)) / @NUMRECORDS as AMOUNT_LAPSEDOTHER

        from dbo.FEPDATA
        where SURVEYYEAR = @YEAR
            and (
                @SUBSECTORCODE = 0
                or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
            );