UFN_BBNC_KPI_ALLREVENUEFORCHANNEL

        CREATE function dbo.UFN_BBNC_KPI_ALLREVENUEFORCHANNEL(@STARTDATE datetime, @ENDDATE datetime)
        returns @BBNCREVENUE table
        (
            REVENUEID uniqueidentifier,
            REVENUESPLITID uniqueidentifier, 
            REVENUETYPECODE tinyint,
            REVENUEAMOUNT money,
            REVENUEDATE datetime
        )
        as
        begin

            insert into @BBNCREVENUE(REVENUEID, REVENUESPLITID, REVENUETYPECODE, REVENUEAMOUNT, REVENUEDATE)
                select
                    REVENUE.ID,
                    REVENUESPLIT.ID,
                    case when REVENUE.TRANSACTIONTYPECODE = 0 then --Gift

                       case when REVENUESPLIT.APPLICATIONCODE = 0 then 0 --Simple Donation

                            when REVENUESPLIT.APPLICATIONCODE = 1 then 6 --Event registration fee

                            when REVENUESPLIT.APPLICATIONCODE = 2 then 4 --Pledge Payment

                          when REVENUESPLIT.APPLICATIONCODE = 3 then 5 --Recurring Gift Payment

                          when REVENUESPLIT.APPLICATIONCODE = 7 then 8 --Matching Gift payment

                          else 0 
                     end
                     when REVENUE.TRANSACTIONTYPECODE = 3 then 3 --Matching Gift Claim

                     when REVENUE.TRANSACTIONTYPECODE = 1 then 1 --Pledge

                     when REVENUE.TRANSACTIONTYPECODE = 2 then 2 --Recurring Gift

                else
                    -1 --Invalid according to the filters for the KPI

                end as [REVENUETYPECODE],
                REVENUESPLIT.AMOUNT,
                REVENUE.DATE
                from dbo.REVENUE
                inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
            inner join dbo.REVENUEBBNC on REVENUEBBNC.ID = REVENUE.ID
                  --Can't filter on REVENUE.DATE here because, even if a pledge (or whatever) doesn't fall in the date range, its payments might. 

              ;

            --chase down payments for any pledges or matching gift claims

            insert into @BBNCREVENUE(REVENUEID, REVENUESPLITID, REVENUETYPECODE,REVENUEAMOUNT, REVENUEDATE)
                --The distinct worries me a little because a single payment can pay multiple installments, I think. But BBNC will only ever create a single installment so it should be fine

                select distinct 
                    REVENUE.ID,
                    REVENUESPLIT.ID,
                    case when [B].REVENUETYPECODE = 1 then 4 --Pledge payment

                         else 8 end, --Matching gift payment

                    REVENUESPLIT.AMOUNT,
                    REVENUE.DATE
                from dbo.REVENUESPLIT
                inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                inner join dbo.INSTALLMENTPAYMENT on INSTALLMENTPAYMENT.PAYMENTID = REVENUESPLIT.ID
                inner join @BBNCREVENUE as [B] on [B].REVENUEID = INSTALLMENTPAYMENT.PLEDGEID
                left join @BBNCREVENUE as [B1] on [B1].REVENUEID = REVENUE.ID and [B1].REVENUESPLITID = REVENUESPLIT.ID
                where [B1].REVENUEID is null --Don't insert duplicates

                  and REVENUE.DATE between @STARTDATE and @ENDDATE;

            insert into @BBNCREVENUE(REVENUEID, REVENUESPLITID, REVENUETYPECODE, REVENUEAMOUNT, REVENUEDATE)
                select
                    REVENUE.ID,
                    REVENUESPLIT.ID,
                    5,
                    REVENUESPLIT.AMOUNT,
                    REVENUE.DATE
                from dbo.REVENUESPLIT
                inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = REVENUESPLIT.ID    
                inner join @BBNCREVENUE as [B] on [B].REVENUEID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
                left join @BBNCREVENUE as [B1] on [B1].REVENUEID = REVENUE.ID and [B1].REVENUESPLITID = REVENUESPLIT.ID
                where [B1].REVENUEID is null --Don't insert duplicates

                  and REVENUE.DATE between @STARTDATE and @ENDDATE;

           --Since we couldn't filter on REVENUE.DATE in the first select, remove any outside of the date from the results before returning

           delete from @BBNCREVENUE 
           where not REVENUEDATE between @STARTDATE and @ENDDATE;

            return;
        end