USP_FAFRAISEDTOTAL_CATEGORIZED_CALCULATE

create procedure [dbo].[USP_FAFRAISEDTOTAL_CATEGORIZED_CALCULATE]
(
        @NUMBERPROCESSED integer = 0 output,
        @CURRENTDATE datetime = null
)
as
begin
set nocount on;

if @CURRENTDATE is null
  set @CURRENTDATE = GETDATE();

begin try

    merge INTO dbo.FAFRAISEDTOTAL_CATEGORIZED as Target 
    using (
        select 
          EVENTID
        , CONSTITUENTID
        , GIFTCOUNT_ALL     
        , GIFTCOUNT_ONLINE
        , GIFTCOUNT_OFFLINE
        , GIFTCOUNT_FROMEMAIL
        , GIFTCOUNT_FROMSOCIALMEDIA
        , GIFTCOUNT_SELF
        , AMOUNT_ALL
        , AMOUNT_ONLINE
        , AMOUNT_OFFLINe
        , AMOUNT_FROMEMAIL
        , AMOUNT_FROMSOCIALMEDIA
        , AMOUNT_SELF
        , AMOUNTPENDING
        , DATECHANGED = @CURRENTDATE

        from dbo.[UFN_REVENUE_CALCPARTICIPANTRAISEDTOTAL_CATEGORIZED] ()
        )
    as source on (target.CONSTITUENTID = source.CONSTITUENTID and target.EVENTID = source.EVENTID)  
    when matched and (
        source.GIFTCOUNT_ALL <> target.GIFTCOUNT_ALL
        or source.GIFTCOUNT_ONLINE <> target.GIFTCOUNT_ONLINE
        or source.GIFTCOUNT_OFFLINE <> target.GIFTCOUNT_OFFLINE
        or source.GIFTCOUNT_FROMEMAIL <> target.GIFTCOUNT_FROMEMAIL
        or source.GIFTCOUNT_FROMSOCIALMEDIA <> target.GIFTCOUNT_FROMSOCIALMEDIA
        or source.GIFTCOUNT_SELF <> target.GIFTCOUNT_SELF
        or source.AMOUNT_ALL <> target.AMOUNT_ALL
        or source.AMOUNT_ONLINE <> target.AMOUNT_ONLINE
        or source.AMOUNT_OFFLINE <> target.AMOUNT_OFFLINE
        or source.AMOUNT_FROMEMAIL <> target.AMOUNT_FROMEMAIL
        or source.AMOUNT_FROMSOCIALMEDIA <> target.AMOUNT_FROMSOCIALMEDIA
        or source.AMOUNT_SELF <> target.AMOUNT_SELF
        or source.AMOUNTPENDING <> target.AMOUNTPENDING
    ) then 
      update set 
        GIFTCOUNT_ALL = isnull(source.GIFTCOUNT_ALL, 0)
        , GIFTCOUNT_ONLINE = isnull(source.GIFTCOUNT_ONLINE, 0)
        , GIFTCOUNT_OFFLINE = isnull(source.GIFTCOUNT_OFFLINE, 0)
        , GIFTCOUNT_FROMEMAIL = isnull(source.GIFTCOUNT_FROMEMAIL, 0)
        , GIFTCOUNT_FROMSOCIALMEDIA = isnull(source.GIFTCOUNT_FROMSOCIALMEDIA, 0)
        , GIFTCOUNT_SELF = isnull(source.GIFTCOUNT_SELF, 0)
        , AMOUNT_ALL = isnull(source.AMOUNT_ALL, 0)
        , AMOUNT_ONLINE = isnull(source.AMOUNT_ONLINE, 0)
        , AMOUNT_OFFLINE = isnull(source.AMOUNT_OFFLINE, 0)
        , AMOUNT_FROMEMAIL = isnull(source.AMOUNT_FROMEMAIL, 0)
        , AMOUNT_FROMSOCIALMEDIA = isnull(source.AMOUNT_FROMSOCIALMEDIA, 0)
        , AMOUNT_SELF = isnull(source.AMOUNT_SELF, 0)
        , AMOUNTPENDING = isnull(source.AMOUNTPENDING, 0)
        , DATECHANGED = source.DATECHANGED

    when not matched then
      insert (EVENTID, CONSTITUENTID
        , GIFTCOUNT_ALL
        , GIFTCOUNT_ONLINE
        , GIFTCOUNT_OFFLINE
        , GIFTCOUNT_FROMEMAIL
        , GIFTCOUNT_FROMSOCIALMEDIA
        , GIFTCOUNT_SELF
        , AMOUNT_ALL
        , AMOUNT_ONLINE
        , AMOUNT_OFFLINE
        , AMOUNT_FROMEMAIL
        , AMOUNT_FROMSOCIALMEDIA
        , AMOUNT_SELF
        , AMOUNTPENDING
        , DATECHANGED

     )  values(source.EVENTID, source.CONSTITUENTID

        , isnull(source.GIFTCOUNT_ALL, 0)
        , isnull(source.GIFTCOUNT_ONLINE, 0)
        , isnull(source.GIFTCOUNT_OFFLINE, 0)
        , isnull(source.GIFTCOUNT_FROMEMAIL, 0)
        , isnull(source.GIFTCOUNT_FROMSOCIALMEDIA, 0)
        , isnull(source.GIFTCOUNT_SELF, 0)
        , isnull(source.AMOUNT_ALL, 0)
        , isnull(source.AMOUNT_ONLINE, 0)
        , isnull(source.AMOUNT_OFFLINE, 0)
        , isnull(source.AMOUNT_FROMEMAIL, 0)
        , isnull(source.AMOUNT_FROMSOCIALMEDIA, 0)
        , isnull(source.AMOUNT_SELF, 0)
        , isnull(source.AMOUNTPENDING, 0)
        , source.DATECHANGED   
      );   

  select @NUMBERPROCESSED=COUNT(*) from dbo.FAFRAISEDTOTAL_CATEGORIZED (nolock)
    where DATECHANGED = @CURRENTDATE 

end try
begin catch
     exec dbo.USP_RAISE_ERROR;
end catch

end