USP_REPORT_FEPBENCHMARKINGCOMPARISON

create procedure dbo.USP_REPORT_FEPBENCHMARKINGCOMPARISON
(
    @YEAR dbo.UDT_YEAR,
    @SUBSECTORCODE tinyint,
    @SITEID uniqueidentifier = null
)
with execute as owner
as    
    declare @TYPE tinyint = 0;        -- Include both organizations and individuals

    declare @PERIOD1STARTDATE datetime = '01/01/' + cast(@YEAR - 1 as nvarchar(10));
    declare @PERIOD1ENDDATE datetime = '12/31/' + cast(@YEAR - 1 as nvarchar(10));
    declare @PERIOD2STARTDATE datetime = '01/01/' + cast(@YEAR as nvarchar(10));
    declare @PERIOD2ENDDATE datetime = '12/31/' + cast(@YEAR as nvarchar(10));

    declare @TEMP table (SEQUENCE integer, CATEGORY nvarchar(50), ORG_NUMDONORS bigint DEFAULT 0, ORG_AMOUNT money DEFAULT 0, FEP_NUMDONORS bigint DEFAULT 0, FEP_AMOUNT money DEFAULT 0, VARIANCE_NUMDONORS bigint DEFAULT 0, VARIANCE_AMOUNT money DEFAULT 0)

    insert into @TEMP (SEQUENCE, CATEGORY, ORG_NUMDONORS, ORG_AMOUNT)
        select
            SEQUENCE,
            CATEGORY,
            case CATEGORY
                when 'Lapsed new' then PERIOD1NUMDONORS
                when 'Lapsed repeat' then PERIOD1NUMDONORS
                else PERIOD2NUMDONORS
            end as ORG_NUMDONORS,
            case CATEGORY
                when 'Upgraded' then PERIOD2AMOUNT - PERIOD1AMOUNT
                when 'Downgraded' then PERIOD2AMOUNT - PERIOD1AMOUNT
                when 'Lapsed new' then -1 * PERIOD1AMOUNT
                when 'Lapsed repeat' then -1 * PERIOD1AMOUNT
                else PERIOD2AMOUNT
            end as ORG_AMOUNT
        from dbo.UFN_FEPSUBMITPROCESS_GETDATA(@TYPE, @PERIOD1STARTDATE,    @PERIOD1ENDDATE, @PERIOD2STARTDATE,    @PERIOD2ENDDATE, @SITEID)
        where CATEGORY <> 'Total';

    -- Retrieve FEP Data and merge with org data

    declare @FEP_NUMRECORDS as integer;
    declare @ALPHABET as nvarchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

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

    if @FEP_NUMRECORDS > 0
        begin            
            merge @TEMP as target
            using (
                select
                    1 as SEQUENCE,
                    'New' as CATEGORY,
                    sum(coalesce(NUMBER_NEW_2, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    sum(coalesce(AMOUNT_NEW_2, 0)) / @FEP_NUMRECORDS as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )

                union all

                select
                    2 as SEQUENCE,
                    'Recaptured' as CATEGORY,
                    sum(coalesce(NUMBER_RECAPTURED_2, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    sum(coalesce(AMOUNT_RECAPTURED_2, 0)) / @FEP_NUMRECORDS as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )

                union all

                select
                    3 as SEQUENCE,
                    'Upgraded' as CATEGORY,
                    sum(coalesce(NUMBER_UPGRADED_2, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    sum(coalesce(AMOUNT_UPGRADED_2, 0) - coalesce(AMOUNT_UPGRADED_1, 0)) / @FEP_NUMRECORDS as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )

                union all

                select
                    4 as SEQUENCE,
                    'Same' as CATEGORY,
                    sum(coalesce(NUMBER_SAME_2, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    sum(coalesce(AMOUNT_SAME_2, 0)) / @FEP_NUMRECORDS as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )

                union all

                select
                    5 as SEQUENCE,
                    'Downgraded' as CATEGORY,
                    sum(coalesce(NUMBER_DOWNGRADED_2, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    sum(coalesce(AMOUNT_DOWNGRADED_2, 0) - coalesce(AMOUNT_DOWNGRADED_1, 0)) / @FEP_NUMRECORDS as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )

                union all

                select
                    6 as SEQUENCE,
                    'Lapsed new' as CATEGORY,
                    sum(coalesce(NUMBER_LAPSED_NEW_1, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    -1 * (sum(coalesce(AMOUNT_LAPSED_NEW_1, 0)) / @FEP_NUMRECORDS) as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )

                union all

                select
                    7 as SEQUENCE,
                    'Lapsed repeat' as CATEGORY,
                    sum(coalesce(NUMBER_LAPSED_OTHER_1, 0)) / @FEP_NUMRECORDS as NUMDONORS,
                    -1 * (sum(coalesce(AMOUNT_LAPSED_OTHER_1, 0)) / @FEP_NUMRECORDS) as AMOUNT
                from dbo.FEPDATA
                where SURVEYYEAR = @YEAR
                    and (
                        @SUBSECTORCODE = 0
                        or (SUBSECTOR = substring(@ALPHABET, @SUBSECTORCODE, 1))
                    )                
            ) as source (SEQUENCE, CATEGORY, NUMDONORS, AMOUNT)
                on target.CATEGORY = source.CATEGORY
            when matched then
                update set
                    FEP_NUMDONORS = source.NUMDONORS,
                    FEP_AMOUNT = source.AMOUNT,
                    VARIANCE_NUMDONORS = target.ORG_NUMDONORS - source.NUMDONORS,
                    VARIANCE_AMOUNT = target.ORG_AMOUNT - source.AMOUNT
            when not matched then
                insert (SEQUENCE, CATEGORY, FEP_NUMDONORS, FEP_AMOUNT, VARIANCE_NUMDONORS, VARIANCE_AMOUNT)
                values (source.SEQUENCE, source.CATEGORY, source.NUMDONORS, source.AMOUNT, source.NUMDONORS * -1, source.AMOUNT * -1);


        end

    select
        SEQUENCE,
        CATEGORY,
        ORG_NUMDONORS,
        ORG_AMOUNT,
        FEP_NUMDONORS,
        FEP_AMOUNT,
        VARIANCE_NUMDONORS,
        VARIANCE_AMOUNT
    from @TEMP
    order by SEQUENCE asc;