UFN_CONSTITUENTRECOGNITION_GETCONSECUTIVESINCE

            CREATE function dbo.UFN_CONSTITUENTRECOGNITION_GETCONSECUTIVESINCE(

                @CONSTITUENTRECOGNITIONID uniqueidentifier
                )returns int
             as
                begin

                    declare @value int;

                    with PROGRAM_NOW as
                        (select ID, EXPIRATIONDATE, RECOGNITIONPROGRAMID, CONSTITUENTID
                         from dbo.CONSTITUENTRECOGNITION
                         where ID = @CONSTITUENTRECOGNITIONID
                        union all
                         select C.ID, C.EXPIRATIONDATE, C.RECOGNITIONPROGRAMID, C.CONSTITUENTID
                         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 @value = datepart(yyyy, min(EXPIRATIONDATE))
                                                from PROGRAM_NOW;

                    return @value;
                end