UFN_GETRECOGNITIONDEFAULTSBETWEENMEMBERSBYBYSOURCE

            create function dbo.UFN_GETRECOGNITIONDEFAULTSBETWEENMEMBERSBYBYSOURCE
            (
                @GROUPID uniqueidentifier,
                @SOURCECONSTITUENTID uniqueidentifier
            )
            returns @specifiedmembers table
            (
                ID uniqueidentifier,
                RECIPIENTCONSTITUENTID uniqueidentifier,
                MATCHFACTOR decimal(5, 2)
            )
            as
            begin          
                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                insert into @specifiedmembers
                (
                    ID,
                    RECIPIENTCONSTITUENTID,
                    MATCHFACTOR
                )
                select
                    RD.ID,
                    RD.RECIPIENTCONSTITUENTID,
                    RD.MATCHFACTOR
                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.SOURCECONSTITUENTID = @SOURCECONSTITUENTID
                    -- 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 > @CURRENTDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))

                return
            end