USP_DATALIST_CONSTITUENT_COMMITTEEMEMBERSHIPLIST

CREATE procedure dbo.USP_DATALIST_CONSTITUENT_COMMITTEEMEMBERSHIPLIST
(
  @CONSTITUENTID uniqueidentifier,
  @INCLUDEINACTIVE bit = 0
)
as
  set nocount on;

  with COMMITTEEMEMBER_CTE as
  (
    select
      GROUPMEMBER.ID,
      GROUPMEMBER.GROUPID,
      CONSTITUENT.NAME as GROUPNAME
    from
      dbo.GROUPMEMBER
    inner join
      dbo.CONSTITUENT on CONSTITUENT.ID = GROUPMEMBER.GROUPID
    where
      GROUPMEMBER.MEMBERID = @CONSTITUENTID and
      dbo.UFN_CONSTITUENT_ISCOMMITTEE(GROUPMEMBER.GROUPID) = 1 and
      (@INCLUDEINACTIVE = 1 or dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1)
  )

  select
    MEMBERSHIP.GROUPID,
    MEMBERSHIP.GROUPNAME as GROUPNAME,
    MEMBERSHIP.ID as GROUPMEMBERID,
    null as PARENT,
    MEMBERSHIP.GROUPNAME as NAME,
    GROUPMEMBERDATERANGE.DATEFROM as STARTDATE,
    GROUPMEMBERDATERANGE.DATETO as ENDDATE,
    GROUPMEMBERDATERANGE.COMMENTS,
    row_number() over (order by MEMBERSHIP.GROUPNAME) as SEQUENCE,
    MEMBERSHIP.ID as ROWID
  from
    COMMITTEEMEMBER_CTE MEMBERSHIP
  left join            -- Warning!  List might not correctly handle multiple date ranges per group membership!

    dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = MEMBERSHIP.ID

  union all

  select
    MEMBERSHIP.GROUPID,
    MEMBERSHIP.GROUPNAME as GROUPNAME,
    MEMBERSHIP.ID as GROUPMEMBERID,
    MEMBERSHIP.ID as PARENT,
    GROUPMEMBERROLECODE.DESCRIPTION as NAME,
    GROUPMEMBERROLE.STARTDATE,
    GROUPMEMBERROLE.ENDDATE,
    '' as COMMENTS,
    row_number() over (order by GROUPMEMBERROLECODE.DESCRIPTION) as SEQUENCE,
    GROUPMEMBERROLE.ID as ROWID
  from
    COMMITTEEMEMBER_CTE MEMBERSHIP
  inner join
    dbo.GROUPMEMBERROLE on GROUPMEMBERROLE.GROUPMEMBERID = MEMBERSHIP.ID
  inner join
    dbo.GROUPMEMBERROLECODE on GROUPMEMBERROLECODE.ID = GROUPMEMBERROLE.GROUPMEMBERROLECODEID;