USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYGROUPMEMBERSTILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYGROUPMEMBERSTILE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ISHOUSEHOLD bit = null output,
    @PRIMARYMEMBERNAME nvarchar(255) = null output,
    @PRIMARYMEMBERID uniqueidentifier = null output,
    @PRIMARYMEMBERDECEASED bit = null output,
    @PRIMARYMEMBERINACTIVE bit = null output,
    @CURRENTMEMBERCOUNT int = null output,
    @FORMERMEMBERCOUNT int = null output
)
as
    set nocount on;

    select 
        @DATALOADED = 1,
        @ISHOUSEHOLD = case when GROUPDATA.GROUPTYPECODE = 0 then 1 else 0 end
    from dbo.CONSTITUENT
    inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
    where CONSTITUENT.ID = @ID;

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

    select
        @PRIMARYMEMBERID = GROUPMEMBER.MEMBERID,
        @PRIMARYMEMBERNAME = NF.NAME,
        @PRIMARYMEMBERDECEASED = case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end,
        @PRIMARYMEMBERINACTIVE = CONSTITUENT.ISINACTIVE
    from dbo.GROUPMEMBER
    inner join dbo.CONSTITUENT on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
    left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GROUPMEMBER.MEMBERID) NF
    where GROUPMEMBER.GROUPID = @ID and GROUPMEMBER.ISPRIMARY = 1;

    select @CURRENTMEMBERCOUNT = coalesce((
        select count(GM.ID)
        from dbo.GROUPMEMBER as GM
        left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
        where
            (
                (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)
            )
            and GM.GROUPID = @ID
    ), 0);

    select @FORMERMEMBERCOUNT = coalesce((
        select count(GM.ID)
        from dbo.GROUPMEMBER as GM
        left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
        where
            (GMDR.DATETO is not null and GMDR.DATETO <= @CURRENTDATEEARLIESTTIME)
            and GM.ID not in (select GROUPMEMBERID from dbo.GROUPMEMBERDATERANGE where DATETO is null)
            and GM.GROUPID = @ID
    ), 0);

    return 0;