USP_DATALIST_CONSTITUENTGROUP_CONSTITUENCY

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

    select
        C.ID,
        C.CONSTITUENCYTYPECODE,
        NF.NAME,
        C.DESCRIPTION,
        dbo.UFN_DATE_FROMFUZZYDATE(C.DATEFROM),
        dbo.UFN_DATE_FROMFUZZYDATE(C.DATETO),
        CONSTITUENT.ID,
        case 
            when CONSTITUENT.ID = @GROUPID then
                '0'
            when GROUPMEMBER.ISPRIMARY = 1 then
                '1'
            else
                '2' + CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36))
        end as 'GROUPORPRIMARYSORT',
        CONSTITUENCYDEFINITION.ISACTIVE
    from
        dbo.GROUPMEMBER
        inner join dbo.CONSTITUENT
            on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
        left join dbo.GROUPMEMBERDATERANGE GMDR
            on GMDR.GROUPMEMBERID = GROUPMEMBER.ID
        cross apply dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(CONSTITUENT.ID) C
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        -- the view should always return all available constituencies, but left joining to ensure Constituent constituencies are never lost.

        left join dbo.V_CONSTITUENCYDISPLAYORDER
            on V_CONSTITUENCYDISPLAYORDER.ID = C.CONSTITUENCYCODEID
        inner join dbo.CONSTITUENCYDEFINITION
            on CONSTITUENCYDEFINITION.ID = C.CONSTITUENCYCODEID
    where
        (
            @INCLUDEINACTIVE = 1 or
            CONSTITUENCYDEFINITION.ISACTIVE = 1
        ) and
        GROUPMEMBER.GROUPID = @GROUPID and
        (
            (GMDR.DATEFROM is null and GMDR.DATETO is null)
            or
            (GMDR.DATEFROM is null and (GMDR.DATETO is not null and GMDR.DATETO >= coalesce(dbo.UFN_DATE_FROMFUZZYDATE(C.DATEFROM), GMDR.DATETO)))
            or
            (GMDR.DATETO is null and (GMDR.DATEFROM is not null and GMDR.DATEFROM <= coalesce(dbo.UFN_DATE_FROMFUZZYDATE(C.DATETO), GMDR.DATEFROM)))
            or
            (
                GMDR.DATEFROM is not null and GMDR.DATEFROM is not null and
                (dbo.UFN_DATE_FROMFUZZYDATE(C.DATEFROM) between GMDR.DATEFROM and GMDR.DATETO) or
                (dbo.UFN_DATE_FROMFUZZYDATE(C.DATETO) between GMDR.DATEFROM and GMDR.DATETO) or
                (GMDR.DATEFROM between coalesce(dbo.UFN_DATE_FROMFUZZYDATE(C.DATEFROM), GMDR.DATEFROM) and coalesce(dbo.UFN_DATE_FROMFUZZYDATE(C.DATETO), GMDR.DATETO))
            )
        )
    order by
        GROUPORPRIMARYSORT desc,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        coalesce(V_CONSTITUENCYDISPLAYORDER.SEQUENCE, 9999),
        C.DESCRIPTION,
        C.DATEFROM,
        C.DATETO,
        C.CONSTITUENCYTYPECODE;