USP_SIMPLEDATALIST_MAILPREFERENCEGROUPCONTACT

                create procedure USP_SIMPLEDATALIST_MAILPREFERENCEGROUPCONTACT
                (
                    @GROUPID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    with GROUPANDMEMBERS(ID, ISPRIMARY, ISGROUP) as
                    (
                        select
                            GM.MEMBERID as ID,
                            GM.ISPRIMARY,
                            0
                        from
                            dbo.GROUPMEMBER GM
                        left outer join
                            dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                        where
                            GM.GROUPID = @GROUPID
                        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))
                        union
                        select 
                            @GROUPID, 0, 1
                    )
                    select
                        C.ID VALUE,
                        case 
                            when GM.ISPRIMARY = 1 then C.NAME + ' (primary contact)'
                            else C.NAME
                        end LABEL
                    from
                        GROUPANDMEMBERS GM
                    left outer join
                        dbo.CONSTITUENT C on C.ID = GM.ID
                    order by
                        GM.ISGROUP desc, GM.ISPRIMARY desc