UFN_CONSTITUENTRECOGNITION_CONSECUTIVEYEARS

            CREATE function dbo.UFN_CONSTITUENTRECOGNITION_CONSECUTIVEYEARS
            (
                @CONSTITUENTRECOGNITIONID uniqueidentifier
            )
            returns int
            with execute as caller
            as begin
                declare @RETVAL int;

                with PROGRAM_NOW as
                (
                    select
                        ID,
                        EXPIRATIONDATE,
                        RECOGNITIONPROGRAMID,
                        CONSTITUENTID,
                        cast(1 as bigint) as ROWNUMBER
                    from dbo.CONSTITUENTRECOGNITION
                    where ID = @CONSTITUENTRECOGNITIONID

                    union all

                    select
                        C.ID,
                        C.EXPIRATIONDATE,
                        C.RECOGNITIONPROGRAMID,
                        C.CONSTITUENTID,
                        ROW_NUMBER() over (partition by C.RECOGNITIONPROGRAMID order by C.EXPIRATIONDATE desc) as ROWNUMBER
                    from dbo.CONSTITUENTRECOGNITION C
                        inner join PROGRAM_NOW P on C.RECOGNITIONPROGRAMID = P.RECOGNITIONPROGRAMID
                        and C.CONSTITUENTID = P.CONSTITUENTID
                        and P.ID <> C.ID
                        and C.EXPIRATIONDATE BETWEEN dateadd(yyyy, -1, P.EXPIRATIONDATE) and DATEADD(dd,-1,P.EXPIRATIONDATE)
                )
                select @RETVAL = count(distinct EXPIRATIONDATE)
                from PROGRAM_NOW
                where ROWNUMBER = 1;

                return @RETVAL;
            end