USP_DATALIST_CONSTITUENT_GROUPMEMBERSHIPCURRENT

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

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

            select
              GD.ID as GROUPID,
              C.NAME as 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 as GROUPTYPE,
                  (select dbo.UDA_BUILDLIST(distinct DESCRIPTION) from 
                        dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(GM.GROUPID) SUBLIST 
                        where 
                          (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) is null and (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) is null or dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) >= @CURRENTDATE))
                              or (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) is null and (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) is null or dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) <= @CURRENTDATE)) 
                              or (dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATEFROM) <= @CURRENTDATE and dbo.UFN_DATE_FROMFUZZYDATE(SUBLIST.DATETO) >= @CURRENTDATE))
              as GROUPCONSTITUENCIES,
              GM.ISPRIMARY,
              dbo.UDA_BUILDLIST(ORDEREDROLES.DESCRIPTION)
            from 
              dbo.GROUPMEMBER GM
            inner join 
              dbo.GROUPDATA GD on GD.ID = GM.GROUPID
            inner join 
              dbo.CONSTITUENT C on C.ID = GD.ID
            outer apply
              (select top 10 GMRC.DESCRIPTION
               from dbo.GROUPMEMBERROLE GMR
               inner join dbo.GROUPMEMBERROLECODE GMRC on GMRC.ID = GMR.GROUPMEMBERROLECODEID
               where GMR.GROUPMEMBERID = GM.ID
               order by case when GMR.ENDDATE is not null then 1 else 0 end,
               GMR.ENDDATE desc, GMR.STARTDATE, GMRC.DESCRIPTION) as ORDEREDROLES
            where 
              GM.MEMBERID = @CONSTITUENTID
            and 
              dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
            group by
              GD.ID, C.ID, C.NAME, GD.GROUPTYPECODE, GD.GROUPTYPEID, GM.ISPRIMARY, GM.GROUPID
            order by
              GD.GROUPTYPECODE, GM.ISPRIMARY desc, C.NAME