USP_GROUPMEMBERROLE_UPDATEDATES

            CREATE procedure dbo.USP_GROUPMEMBERROLE_UPDATEDATES
            (
                @GROUPMEMBERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

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

                update dbo.GROUPMEMBERROLE set
                    STARTDATE = case when GMR.STARTDATE < GROUPMEMBERDATERANGE.DATEFROM then GROUPMEMBERDATERANGE.DATEFROM 
                                    when GMR.STARTDATE > GROUPMEMBERDATERANGE.DATETO then GROUPMEMBERDATERANGE.DATETO
                                    when GMR.STARTDATE is null and GROUPMEMBERDATERANGE.DATEFROM is not null then GROUPMEMBERDATERANGE.DATEFROM
                                    else GMR.STARTDATE end,
                    ENDDATE = case when GMR.ENDDATE > GROUPMEMBERDATERANGE.DATETO then GROUPMEMBERDATERANGE.DATETO
                                when GMR.ENDDATE < GROUPMEMBERDATERANGE.DATEFROM then GROUPMEMBERDATERANGE.DATEFROM
                                when GMR.ENDDATE is null and GROUPMEMBERDATERANGE.DATETO is not null then GROUPMEMBERDATERANGE.DATETO
                                else GMR.ENDDATE end,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from dbo.GROUPMEMBERROLE GMR
                inner join dbo.GROUPMEMBER on GROUPMEMBER.ID = GMR.GROUPMEMBERID
                inner join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                where GMR.GROUPMEMBERID = @GROUPMEMBERID

                return 0;