UFN_SELECTION_CONSTITUENT_LOYALDONORS

        CREATE function dbo.UFN_SELECTION_CONSTITUENT_LOYALDONORS()
        returns table
        as return
        (

            with CONSTITUENCYCRITERIASPOUSE_CTE as
            (
                select top 1 
                    LOYALDONORCONSTITUENCYDEFPERIOD, 
                    LOYALDONORCONSTITUENCYDEFPERIODUNITCODE,
                    MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION,
                    getdate() as CURRENTDATE,
                    dateadd(mm,-LOYALDONORCONSTITUENCYDEFPERIOD,getdate()) MMFROMCURRENTDATE,
                    dateadd(yy,-LOYALDONORCONSTITUENCYDEFPERIOD,getdate()) YYFROMCURRENTDATE
                from dbo.CONSTITUENCYCRITERIASPOUSE
            )
            select CONSTITUENTID as ID from
            (
                select distinct REVENUEANDRECOGNITION.CONSTITUENTID, REVENUEANDRECOGNITION.PERIOD from 
                (
                    select distinct 
                        REVENUE.CONSTITUENTID, 
                        datediff(mm,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE) as PERIOD
                    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock) on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
                    cross join CONSTITUENCYCRITERIASPOUSE_CTE
                    where 
                        REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        cast(REVENUE.DATE as datetime) >= CONSTITUENCYCRITERIASPOUSE_CTE.MMFROMCURRENTDATE and 
                        REVENUE.DELETEDON is null and FTLT.DELETEDON is null
                        and FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    group by 
                        REVENUE.CONSTITUENTID, 
                        datediff(mm,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE)

                    union all

                    select distinct 
                        REVENUERECOGNITION.CONSTITUENTID, 
                        datediff(mm,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE) as PERIOD
                    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT with (nolock) on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.REVENUERECOGNITION with (nolock) on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    cross join CONSTITUENCYCRITERIASPOUSE_CTE
                    where 
                        REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        cast(REVENUE.DATE as datetime) >= CONSTITUENCYCRITERIASPOUSE_CTE.MMFROMCURRENTDATE and 
                        REVENUESPLIT.DELETEDON is null and
                        REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID
                        and REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    group by 
                        REVENUERECOGNITION.CONSTITUENTID, 
                        datediff(mm,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE)
                ) as REVENUEANDRECOGNITION
                group by REVENUEANDRECOGNITION.CONSTITUENTID, PERIOD
            ) as SUBSQL 
            cross join CONSTITUENCYCRITERIASPOUSE_CTE
            where
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1 and
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIODUNITCODE = 1
            group by 
                SUBSQL.CONSTITUENTID,
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD
            having count(*) >= CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD

            union all

            select CONSTITUENTID as ID from
            (
                select distinct REVENUEANDRECOGNITION.CONSTITUENTID, REVENUEANDRECOGNITION.PERIOD from 
                (
                    select distinct 
                        REVENUE.CONSTITUENTID, 
                        datediff(yy,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE) as PERIOD
                    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock) on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
                    cross join CONSTITUENCYCRITERIASPOUSE_CTE
                    where 
                        REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        cast(REVENUE.DATE as datetime) >= CONSTITUENCYCRITERIASPOUSE_CTE.YYFROMCURRENTDATE and 
                        REVENUE.DELETEDON is null and FTLT.DELETEDON is null
                        and FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    group by 
                        REVENUE.CONSTITUENTID, 
                        datediff(yy,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE)

                    union all

                    select distinct
                        REVENUERECOGNITION.CONSTITUENTID, 
                        datediff(yy,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE) as PERIOD
                    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT with (nolock) on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.REVENUERECOGNITION with (nolock) on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    cross join CONSTITUENCYCRITERIASPOUSE_CTE
                    where 
                        REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        cast(REVENUE.DATE as datetime) >= CONSTITUENCYCRITERIASPOUSE_CTE.YYFROMCURRENTDATE and 
                        REVENUESPLIT.DELETEDON is null and
                        REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID
                        and REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    group by 
                        REVENUERECOGNITION.CONSTITUENTID, 
                        datediff(yy,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE)
                ) as REVENUEANDRECOGNITION
                group by REVENUEANDRECOGNITION.CONSTITUENTID, PERIOD
            ) as SUBSQL 
            cross join CONSTITUENCYCRITERIASPOUSE_CTE
            where
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1 and
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIODUNITCODE = 0
            group by 
                CONSTITUENTID,
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD
            having count(*) >= CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD

            union all

            select CONSTITUENTID as ID from
            (
                select distinct 
                    CONSTITUENTID, 
                    datediff(mm,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE) as PERIOD
                from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock) on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
                cross join CONSTITUENCYCRITERIASPOUSE_CTE
                where 
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    cast(REVENUE.DATE as datetime) >= CONSTITUENCYCRITERIASPOUSE_CTE.MMFROMCURRENTDATE and 
                    REVENUE.DELETEDON is null and FTLT.DELETEDON is null
                    and FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                group by 
                    CONSTITUENTID, 
                    datediff(mm,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE)
            ) as SUBSQL 
            cross join CONSTITUENCYCRITERIASPOUSE_CTE
            where
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 0 and
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIODUNITCODE = 1
            group by 
                CONSTITUENTID,
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD
            having count(*) >= CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD

            union all

            select CONSTITUENTID as ID from
            (
                select distinct 
                    CONSTITUENTID, 
                    datediff(yy,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE) as PERIOD
                from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
                inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock) on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
                cross join CONSTITUENCYCRITERIASPOUSE_CTE
                where 
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    cast(REVENUE.DATE as datetime) >= CONSTITUENCYCRITERIASPOUSE_CTE.YYFROMCURRENTDATE and 
                    REVENUE.DELETEDON is null and FTLT.DELETEDON is null
                    and FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                group by 
                    CONSTITUENTID, 
                    datediff(yy,cast(REVENUE.DATE as datetime),CONSTITUENCYCRITERIASPOUSE_CTE.CURRENTDATE)
            ) as SUBSQL 
            cross join CONSTITUENCYCRITERIASPOUSE_CTE
            where
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 0 and
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIODUNITCODE = 0
            group by 
                CONSTITUENTID,
                CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD
            having count(*) >= CONSTITUENCYCRITERIASPOUSE_CTE.LOYALDONORCONSTITUENCYDEFPERIOD


        )