UFN_HOUSEHOLDRECOGNITIONS_GETMEMBERSPECIFIEDMEMBERS

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

                with GROUPMEMBERSCTE as
                (
                    select
                        GM.MEMBERID 
                    from dbo.GROUPMEMBER GM
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    where GM.GROUPID = @GROUPID
                    -- 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))                                    
                )          
                insert into @specifiedmembers
                (
                    ID,
                    SOURCECONSTITUENTID,
                    RECIPIENTCONSTITUENTID,
                    MATCHFACTOR,
                    REVENUERECOGNITIONTYPECODEID
                )
                select
                    RD.ID,
                    RD.SOURCECONSTITUENTID,
                    RD.RECIPIENTCONSTITUENTID,
                    RD.MATCHFACTOR,
                    RD.REVENUERECOGNITIONTYPECODEID
                from dbo.REVENUERECOGNITIONDEFAULT RD
                inner join dbo.CONSTITUENT C on RD.RECIPIENTCONSTITUENTID = C.ID
                where 
                    RD.SOURCECONSTITUENTID in (select MEMBERID from GROUPMEMBERSCTE) and
                    RD.RECIPIENTCONSTITUENTID in (select MEMBERID from GROUPMEMBERSCTE) and
                    -- Exclude records with PREVENTRECOGNITIONSDEFAULTING set to true

                    -- since that acts as flag to indicate the recognition default

                    -- no longer exists

                    PREVENTRECOGNITIONSDEFAULTING = 0
                order by C.KEYNAME, C.FIRSTNAME

                return
            end