USP_DATALIST_CONSTITUENT_GROUPMEMBERSHIPS

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

            declare @CURRENTDATEEARLIESTTIME date;
            set @CURRENTDATEEARLIESTTIME = getdate();

            select
              GD.ID,
              C.NAME GROUPNAME,
              case
                when GD.GROUPTYPECODE = 0 then 'Household'
                when GD.GROUPTYPECODE = 1 then (select GT.NAME from dbo.GROUPTYPE GT where GT.ID = GD.GROUPTYPEID)
              end GROUPTYPE,
              GM.ISPRIMARY,
              case when ((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))
                then cast(1 as bit)
                else cast(0 as bit)
              end MEMBERSHIPISACTIVE
            from dbo.GROUPMEMBER GM
            inner join dbo.GROUPDATA GD on GD.ID = GM.GROUPID
            inner join dbo.CONSTITUENT C on C.ID = GD.ID
            left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
            where GM.MEMBERID = @CONSTITUENTID