UFN_GETRECOGNITIONDEFAULTSBETWEENMEMBERSBYRECIPIENT

            CREATE function dbo.UFN_GETRECOGNITIONDEFAULTSBETWEENMEMBERSBYRECIPIENT
            (
                @GROUPID uniqueidentifier,
                @RECIPIENTCONSTITUENTID uniqueidentifier
            )
            returns @specifiedmembers table
            (
                ID uniqueidentifier,
                SOURCECONSTITUENTID uniqueidentifier,
                MATCHFACTOR decimal(5, 2),
                REVENUERECOGNITIONTYPECODEID uniqueidentifier
            )
            as
            begin          
                declare @CURRENTDATEEARLIESTTIME date;
                set @CURRENTDATEEARLIESTTIME = getdate();

                -- Return recognition defaults for the constituent where the constituent is the recipient

                -- and the source is a member of the household

                insert into @specifiedmembers
                (
                    ID,
                    SOURCECONSTITUENTID,
                    MATCHFACTOR,
                    REVENUERECOGNITIONTYPECODEID
                )
                select
                    RD.ID,
                    RD.SOURCECONSTITUENTID,
                    RD.MATCHFACTOR,
                    RD.REVENUERECOGNITIONTYPECODEID
                from dbo.GROUPMEMBER GM
                inner join dbo.REVENUERECOGNITIONDEFAULT RD on GM.MEMBERID = RD.SOURCECONSTITUENTID
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                where 
                    GM.GROUPID = @GROUPID and
                    RD.RECIPIENTCONSTITUENTID = @RECIPIENTCONSTITUENTID
                    -- the GMDR.DATETO is set to the earliest time on that day, so use a strictly greater than in checks using current day

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                    or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))

                return
            end