USP_FAFGROUP_CALCULATE_SOCIALMEDIAPOSTCOUNT

create procedure dbo.USP_FAFGROUP_CALCULATE_SOCIALMEDIAPOSTCOUNT
(
        @NUMBERPROCESSED integer = 0 output,
        @CHANGEAGENTID uniqueidentifier = null,
        @CURRENTDATE datetime = null
)
as
begin
set nocount on;

if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

begin try

merge into dbo.FAFGROUPSUMMARYINFORMATION_CATEGORIZED as Target
    using
(    
    select 
    AllMembers.[GROUPID], 
    AllMembers.[EVENTID], 
    COUNT_SOCIALMEDIAPOSTS = count (*)
    from dbo.[FAFEVENTSOCIALNETWORKPOST] SN (nolock)
    inner join dbo.[REGISTRANT] R (nolock) on SN.[REGISTRANTID] = R.[ID]
    inner join 
    (    
        select GROUPID = G.[PARENTID], G.[TEAMID], TF.[CONSTITUENTID], G.[EVENTID] 
        from dbo.[UFN_FAFGROUP_GETALLGROUPS]() G
        inner join dbo.[TEAMFUNDRAISINGTEAMMEMBER] TFTM (nolock) on TFTM.[TEAMFUNDRAISINGTEAMID] = G.[TeamID]
        inner join dbo.[TEAMFUNDRAISER] TF (nolock) on TF.[ID] = TFTM.[TEAMFUNDRAISERID]
        group by G.[PARENTID], G.[TEAMID], G.[EVENTID], TF.[CONSTITUENTID]
    ) AllMembers
    on R.[CONSTITUENTID] = AllMembers.[CONSTITUENTID] and R.[EVENTID] = AllMembers.[EVENTID]
    group by AllMembers.[GROUPID], AllMembers.[EVENTID]
) as source on Target.ID = source.GROUPID and Target.EVENTID = source.EventID   

when matched and (source.COUNT_SOCIALMEDIAPOSTS <> Target.COUNT_SOCIALMEDIAPOSTS) then
    update set
        COUNT_SOCIALMEDIAPOSTS = source.COUNT_SOCIALMEDIAPOSTS,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED   = @CURRENTDATE        
when not matched then
    insert(
        ID, 
        EVENTID, 
        COUNT_SOCIALMEDIAPOSTS,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
    )
    values(
        source.GROUPID, 
        source.EVENTID, 
        COUNT_SOCIALMEDIAPOSTS,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
    );    

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

end try
begin catch
     exec dbo.USP_RAISE_ERROR;
end catch

end