UFN_REPORT_VALIDCONSCODE

CREATE function dbo.UFN_REPORT_VALIDCONSCODE
(
  @STARTDATE datetime = null,
  @ENDDATE datetime=null
)
returns table
return
(
    select
        1 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        BOARDMEMBERDATERANGE.CONSTITUENTID,
        BOARDMEMBERDATERANGE.DATEFROM,
        BOARDMEMBERDATERANGE.DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.BOARDMEMBERDATERANGE
    where CONSTITUENCYDEFINITION.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            (
                BOARDMEMBERDATERANGE.DATEFROM is null
                and BOARDMEMBERDATERANGE.DATETO is null
                )
            or (
                BOARDMEMBERDATERANGE.DATEFROM is null
                and BOARDMEMBERDATERANGE.DATETO >= @STARTDATE
                )
            or (
                BOARDMEMBERDATERANGE.DATEFROM <= @ENDDATE
                and BOARDMEMBERDATERANGE.DATETO is null
                )
            or (
                BOARDMEMBERDATERANGE.DATEFROM <= @ENDDATE
                and BOARDMEMBERDATERANGE.DATETO >= @STARTDATE
                )
            )

    union all

    select
        2 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        STAFFDATERANGE.CONSTITUENTID,
        STAFFDATERANGE.DATEFROM,
        STAFFDATERANGE.DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.STAFFDATERANGE
    where CONSTITUENCYDEFINITION.ID = '6093915E-ADE9-42BE-88AE-304731754467'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            (
                STAFFDATERANGE.DATEFROM is null
                and STAFFDATERANGE.DATETO is null
                )
            or (
                STAFFDATERANGE.DATEFROM is null
                and STAFFDATERANGE.DATETO >= @STARTDATE
                )
            or (
                STAFFDATERANGE.DATEFROM <= @ENDDATE
                and STAFFDATERANGE.DATETO is null
                )
            or (
                STAFFDATERANGE.DATEFROM <= @ENDDATE
                and STAFFDATERANGE.DATETO >= @STARTDATE
                )
            )

    union all

    select
        3 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        FUNDRAISERDATERANGE.CONSTITUENTID,
        FUNDRAISERDATERANGE.DATEFROM,
        FUNDRAISERDATERANGE.DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.FUNDRAISERDATERANGE
    where CONSTITUENCYDEFINITION.ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            (
                FUNDRAISERDATERANGE.DATEFROM is null
                and FUNDRAISERDATERANGE.DATETO is null
                )
            or (
                FUNDRAISERDATERANGE.DATEFROM is null
                and FUNDRAISERDATERANGE.DATETO >= @STARTDATE
                )
            or (
                FUNDRAISERDATERANGE.DATEFROM <= @ENDDATE
                and FUNDRAISERDATERANGE.DATETO is null
                )
            or (
                FUNDRAISERDATERANGE.DATEFROM <= @ENDDATE
                and FUNDRAISERDATERANGE.DATETO >= @STARTDATE
                )
            )

    union all

    select
        4 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        PROSPECTDATERANGE.CONSTITUENTID,
        PROSPECTDATERANGE.DATEFROM,
        PROSPECTDATERANGE.DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.PROSPECTDATERANGE
    where CONSTITUENCYDEFINITION.ID = '00E748FB-940D-4A7D-A133-C148B29410A8'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            (
                PROSPECTDATERANGE.DATEFROM is null
                and PROSPECTDATERANGE.DATETO is null
                )
            or (
                PROSPECTDATERANGE.DATEFROM is null
                and PROSPECTDATERANGE.DATETO >= @STARTDATE
                )
            or (
                PROSPECTDATERANGE.DATEFROM <= @ENDDATE
                and PROSPECTDATERANGE.DATETO is null
                )
            or (
                PROSPECTDATERANGE.DATEFROM <= @ENDDATE
                and PROSPECTDATERANGE.DATETO >= @STARTDATE
                )
            )

    union all

    select
        5 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        VOLUNTEERDATERANGE.CONSTITUENTID,
        VOLUNTEERDATERANGE.DATEFROM,
        VOLUNTEERDATERANGE.DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.VOLUNTEERDATERANGE
    where CONSTITUENCYDEFINITION.ID = 'E7489703-3D63-4017-A2BC-88C092563C5D'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            (
                VOLUNTEERDATERANGE.DATEFROM is null
                and VOLUNTEERDATERANGE.DATETO is null
                )
            or (
                VOLUNTEERDATERANGE.DATEFROM is null
                and VOLUNTEERDATERANGE.DATETO >= @STARTDATE
                )
            or (
                VOLUNTEERDATERANGE.DATEFROM <= @ENDDATE
                and VOLUNTEERDATERANGE.DATETO is null
                )
            or (
                VOLUNTEERDATERANGE.DATEFROM <= @ENDDATE
                and VOLUNTEERDATERANGE.DATETO >= @STARTDATE
                )
            )

    union all

    select
        CODETYPE,
        V.ID,
        V.DESCRIPTION,
        CONSTITUENTID,
        DATEFROM,
        DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join (
        select 6 "CODETYPE",
            case 
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 0
                    then STUDENTNAME.ID
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 1
                    then ALUMNUSNAME.ID
                else null
                end "ID",
            case 
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 0
                    then STUDENTNAME.DESCRIPTION
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 1
                    then ALUMNUSNAME.DESCRIPTION
                else null
                end "DESCRIPTION",
            EH.CONSTITUENTID,
            case 
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 0
                    then min(dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(EH.STARTDATE))
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 1
                    then max(dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(EH.STARTDATE))
                else null
                end "DATEFROM",
            case 
                when max(EHS.CONSTITUENCYIMPLIEDCODE) = 0
                    then max(dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(EH.ENDDATE))
                else null
                end "DATETO"
        from dbo.EDUCATIONALHISTORY EH
        cross apply dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE2(EH.ID) as EHSLINKING
        inner join dbo.EDUCATIONALHISTORYSTATUS EHS
            on EHSLINKING.EDUCATIONALHISTORYSTATUSID = EHS.ID
        left join dbo.CONSTITUENCYDEFINITION STUDENTNAME
            on STUDENTNAME.ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC'
            and STUDENTNAME.ISACTIVE = 1
        left join dbo.CONSTITUENCYDEFINITION ALUMNUSNAME
            on ALUMNUSNAME.ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592'
            and ALUMNUSNAME.ISACTIVE = 1
        where EH.EDUCATIONALINSTITUTIONID in (
                select ID
                from dbo.SCHOOL
                )
        group by EH.CONSTITUENTID,
            STUDENTNAME.ID,
            STUDENTNAME.DESCRIPTION,
            ALUMNUSNAME.ID,
            ALUMNUSNAME.DESCRIPTION
        ) V
        on V.ID = CONSTITUENCYID
    where (
            V.DATEFROM is null
            and V.DATETO is null
            )
        or (
            V.DATEFROM is null
            and V.DATETO >= @STARTDATE
            )
        or (
            V.DATEFROM <= @ENDDATE
            and V.DATETO is null
            )
        or (
            V.DATEFROM <= @ENDDATE
            and V.DATETO >= @STARTDATE
            )

    union all

    select
        CODETYPE,
        V.ID,
        V.DESCRIPTION,
        CONSTITUENTID,
        DATEFROM,
        null "DATETO"
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join (
        select 7 "CODETYPE",
            CONSTITUENCYDEFINITION.ID,
            CONSTITUENCYDEFINITION.DESCRIPTION,
            NETCOMMUNITYCLIENTUSER.CONSTITUENTID,
            --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

            cast(cast(isnull(NETCOMMUNITYCLIENTUSER.USERADDEDDATE, NETCOMMUNITYCLIENTUSER.SIGNUPTRANSACTIONDATE) as date) as datetime) "DATEFROM",
            null "DATETO"
        from dbo.CONSTITUENT
        left join dbo.NETCOMMUNITYCLIENTUSER
            on CONSTITUENT.ID = NETCOMMUNITYCLIENTUSER.CONSTITUENTID
        left join dbo.CONSTITUENCYDEFINITION
            on CONSTITUENCYDEFINITION.ID = '55FE8E7C-2B68-44C8-B35C-818AD1944C03'
            and CONSTITUENCYDEFINITION.ISACTIVE = 1
        where NETCOMMUNITYCLIENTUSER.ACTIVE = 1
            and NETCOMMUNITYCLIENTUSER.DELETED = 0
        ) V
        on V.ID = CONSTITUENCYID
    where (
            V.DATEFROM <= @ENDDATE
            or V.DATEFROM is null
            )

    union all

    select
        8 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        V.CONSTITUENTID,
        V.DATEFROM,
        null "DATETO"
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join (
        select REGISTRANT.CONSTITUENTID,
            min(REGISTRANT.DATEADDED) "DATEFROM"
        from dbo.REGISTRANT
        where (
                REGISTRANT.DATEADDED <= @ENDDATE
                or REGISTRANT.DATEADDED is null
                )
        group by REGISTRANT.CONSTITUENTID
        ) V
    where CONSTITUENCYDEFINITION.ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            V.DATEFROM <= @ENDDATE
            or V.DATEFROM is null
            )

    union all

    select
        9 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        C.ID,
        null "DATEFROM",
        null "DATETO"
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.constituent C
    where CONSTITUENCYDEFINITION.ID = '5435C96D-8617-46C3-9A62-5AFF08451A53'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and exists (
            select ID
            from dbo.EVENTEXPENSE
            where VENDORID = C.ID
            )

    union all

    select
        10 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        V.CONSTITUENTID,
        V.DATEFROM,
        null "DATETO"
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join (
        select FIRSTREVENUEDATE as DATEFROM,
            CONSTITUENTID
        from dbo.REVENUELIFECYCLE
        inner join dbo.LIFECYCLETYPE
            on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
        where LIFECYCLETYPE.NAME in (
                'Consecutive years donor',
                'First year donor',
                'Loyal donor',
                'Major donor',
                'Mid-level donor',
                'Multiple years donor'
                )
            and LIFECYCLETYPE.LIFECYCLEGROUPCODE = 0
            and REVENUELIFECYCLE.LASTREVENUEDATE <= @ENDDATE

        union

        select
            min(DATE) DATEFROM,
            CONSTITUENTID
        from dbo.REVENUE r
        inner join dbo.REVENUESPLIT rs
            on rs.REVENUEID = r.ID
        where (r.DATE <= @ENDDATE)
            and (
                (r.TRANSACTIONTYPECODE = 1)
                or (
                    r.TRANSACTIONTYPECODE = 0
                    and (
                        rs.APPLICATIONCODE in (
                            0,
                            3
                            )
                        or (
                            RS.APPLICATIONCODE = 1
                            and RS.TYPECODE = 0
                            )
                        )
                    )
                )
            and r.CONSTITUENTID not in (
                select CONSTITUENTID
                from dbo.REVENUELIFECYCLE
                )
        group by r.CONSTITUENTID
        ) V
    where CONSTITUENCYDEFINITION.ID = '70165682-4324-46EC-9439-83FC0CC67E7F'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1

    union all

    select
        11 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        V.CONSTITUENTID,
        V.DATEFROM,
        null "DATETO"
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join (
        select min(DATEADDED) "DATEFROM",
            RELATIONSHIPCONSTITUENTID "CONSTITUENTID"
        from dbo.RELATIONSHIP
        where (
                RELATIONSHIP.DATEADDED <= @ENDDATE
                or RELATIONSHIP.DATEADDED is null
                )
        group by RELATIONSHIPCONSTITUENTID
        ) V
    where CONSTITUENCYDEFINITION.ID = '8B6ECCFD-6829-4FBC-B092-58CA083F9322'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1

    union all

    select
        12 "CODETYPE",
        CONSTITUENCYDEFINITION.ID,
        CONSTITUENCYDEFINITION.DESCRIPTION,
        GRANTOR.ID,
        GRANTOR.DATEADDED,
        null "DATETO"
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYDEFINITION.ID = VSECONSTITUENCYMAP.CONSTITUENCYID
    cross join dbo.GRANTOR
    where CONSTITUENCYDEFINITION.ID = 'D9982C99-15C1-4C90-873E-56FD4B164056'
        and CONSTITUENCYDEFINITION.ISACTIVE = 1
        and (
            GRANTOR.DATEADDED >= @STARTDATE
            and GRANTOR.DATEADDED <= @ENDDATE
            )

    union all

    select
        99 "CODETYPE",
        CODE.ID,
        CODE.DESCRIPTION,
        CONSTITUENCY.CONSTITUENTID,
        CONSTITUENCY.DATEFROM,
        CONSTITUENCY.DATETO
    from dbo.VSECONSTITUENCYMAP
    inner join dbo.VSECONSTITUENCY
        on VSECONSTITUENCY.ID = VSECONSTITUENCYMAP.VSECONSTITUENCYID
    inner join dbo.CONSTITUENCY
        on CONSTITUENCY.CONSTITUENCYCODEID = VSECONSTITUENCYMAP.CONSTITUENCYID
    inner join dbo.CONSTITUENCYDEFINITION as CODE
        on CODE.ID = CONSTITUENCY.CONSTITUENCYCODEID
    where (
            CONSTITUENCY.DATEFROM is null
            and CONSTITUENCY.DATETO is null
            )
        or (
            CONSTITUENCY.DATEFROM is null
            and CONSTITUENCY.DATETO >= @STARTDATE
            )
        or (
            CONSTITUENCY.DATEFROM <= @ENDDATE
            and CONSTITUENCY.DATETO is null
            )
        or (
            CONSTITUENCY.DATEFROM <= @ENDDATE
            and CONSTITUENCY.DATETO >= @STARTDATE
            )
)