USP_FAFGROUP_CALCULATE_PARTICIPANTRETENTIONRATE

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

--declare @CURRENTDATE datetime;


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

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

-- get total number of participant returned to a group and its retention rate, 


begin try
    merge into dbo.FAFGROUPSUMMARYINFORMATION as Target
    using
    (    
        select A.GROUPID, A.EVENTID, A.CONSTITUENTID, A.TYPECODE , A.TOTALPARTICIPANTSRETAINED, A.PARTICIPANTRETENTIONRATE
        from dbo.UFN_FAFGROUP_GETPARTICIPANTRETENTIONRATE() A 

    ) as source on Target.ID = source.GROUPID and Target.EVENTID = source.EventID         
    when matched and (source.TOTALPARTICIPANTSRETAINED <> Target.TOTALPARTICIPANTSRETAINED or source.PARTICIPANTRETENTIONRATE <> Target.PARTICIPANTRETENTIONRATE) then
        update set
            TOTALPARTICIPANTSRETAINED = source.TOTALPARTICIPANTSRETAINED,
            PARTICIPANTRETENTIONRATE = source.PARTICIPANTRETENTIONRATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED   = @CURRENTDATE        
    when not matched then
        insert(ID, GROUPTYPECODE, EVENTID, GROUPCONSTITUENTID, TOTALPARTICIPANTSRETAINED, PARTICIPANTRETENTIONRATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(source.GROUPID, source.TYPECODE, source.EventID, source.CONSTITUENTID, source.TOTALPARTICIPANTSRETAINED, source.PARTICIPANTRETENTIONRATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

end try
begin catch
 exec dbo.USP_RAISE_ERROR;        
end catch

end