USP_FAFGROUP_CALCULATE_GROUPRETENTIONRATE

CREATE procedure dbo.USP_FAFGROUP_CALCULATE_GROUPRETENTIONRATE
(
                @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 group returned and retention rate, 


begin try
    merge into dbo.FAFGROUPSUMMARYINFORMATION as Target
    using
    (    
        select A.GROUPID, A.EVENTID, A.CONSTITUENTID, A.TYPECODE , A.TOTALGROUPSRETAINED, A.GROUPRETENTIONRATE
        from dbo.UFN_FAFGROUP_GETGROUPRETENTIONRATE() A 

    ) as source on Target.ID = source.GROUPID and Target.EVENTID = source.EventID     
    when matched and (source.TOTALGROUPSRETAINED <> Target.TOTALGROUPSRETAINED or source.GROUPRETENTIONRATE <> Target.GROUPRETENTIONRATE) then
        update set
            TOTALGROUPSRETAINED = source.TOTALGROUPSRETAINED,
            GROUPRETENTIONRATE = source.GROUPRETENTIONRATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED   = @CURRENTDATE        
    when not matched then
        insert(ID, GROUPTYPECODE, EVENTID, GROUPCONSTITUENTID, TOTALGROUPSRETAINED, GROUPRETENTIONRATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(source.GROUPID, source.TYPECODE, source.EventID, source.CONSTITUENTID, source.TOTALGROUPSRETAINED, source.GROUPRETENTIONRATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end try
begin catch
   exec dbo.USP_RAISE_ERROR;
end catch

end