USP_DATALIST_CONSTITUENTSRECOGNITIONDEFAULTSWITHHOUSEHOLDMEMBERS

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTSRECOGNITIONDEFAULTSWITHHOUSEHOLDMEMBERS
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @GROUPID uniqueidentifier
                    )
                    as
                        set nocount on;

                        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))                                    
                        )
                        select
                            SOURCECONSTITUENTID,
                            RECIPIENTCONSTITUENTID,
                            MATCHFACTOR
                        from dbo.REVENUERECOGNITIONDEFAULT RM
                        left join dbo.CONSTITUENT SC on SC.ID = RM.SOURCECONSTITUENTID
                        left join dbo.CONSTITUENT RC on RC.ID = RM.RECIPIENTCONSTITUENTID
                        where
                            (SOURCECONSTITUENTID = @CONSTITUENTID and
                            RECIPIENTCONSTITUENTID in (select MEMBERID from GROUPMEMBERSCTE)) or
                            (SOURCECONSTITUENTID in (select MEMBERID from GROUPMEMBERSCTE) and
                            RECIPIENTCONSTITUENTID = @CONSTITUENTID)
                        order by SC.KEYNAME, SC.FIRSTNAME, RC.KEYNAME, RC.FIRSTNAME