USP_SIMPLEDATALIST_OPPORTUNITYDESIGNATIONCANDIDATE

                CREATE procedure dbo.USP_SIMPLEDATALIST_OPPORTUNITYDESIGNATIONCANDIDATE
        (
          @CONSTITUENTID uniqueidentifier
        )
        as
          set nocount on;

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

          -- group members

          select
            C.ID as 'VALUE',
            C.NAME as 'LABEL'
          from
            dbo.GROUPMEMBER GM
          inner join
            dbo.CONSTITUENT C on GM.MEMBERID = C.ID
          left join 
            dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
          where
            GM.GROUPID = @CONSTITUENTID
          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))