UFN_CONSTITUENT_GETCURRENTRECOGNITIONPROGRAMS

            CREATE function dbo.UFN_CONSTITUENT_GETCURRENTRECOGNITIONPROGRAMS
            (
                @CURRENTDATE datetime
            )
            returns table
            as
            return
            (
                with 
                [PROGRAMS] as 
                (
                    select 
                        CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID, 
                        case when max(EXPIRATIONDATE) is null or max(EXPIRATIONDATE) >= @CURRENTDATE then 0 else 1 end [EXPIRED],
                        CONSTITUENTID
                    from dbo.CONSTITUENTRECOGNITION
                    group by RECOGNITIONPROGRAMID, CONSTITUENTID
                ),
                [EXPIRATIONS] as
                (
                    select 
                        CONSTITUENTRECOGNITION.ID,
                        CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID,
                        CONSTITUENTRECOGNITION.JOINDATE,
                        case when CONSTITUENTRECOGNITION.EXPIRATIONDATE is null or CONSTITUENTRECOGNITION.EXPIRATIONDATE >= @CURRENTDATE then 0 else 1 end [EXPIRED],
                        CONSTITUENTRECOGNITION.CONSTITUENTID,
                        CONSTITUENTRECOGNITION.STATUSCODE,
                        RECOGNITIONLEVEL.AMOUNT as RECOGNITIONLEVELAMOUNT
                    from dbo.CONSTITUENTRECOGNITION
                    inner join dbo.RECOGNITIONLEVEL on RECOGNITIONLEVEL.ID = CONSTITUENTRECOGNITION.RECOGNITIONLEVELID
                )
                select 
                    LEVEL.ID,
                    PROGRAMS.EXPIRED,
                    PROGRAMS.CONSTITUENTID
                from [PROGRAMS]
                cross apply 
                (
                    select top 1 [EXPIRATIONS].ID
                    from [EXPIRATIONS]
                    where 
                        [PROGRAMS].RECOGNITIONPROGRAMID = [EXPIRATIONS].RECOGNITIONPROGRAMID and
                        [PROGRAMS].CONSTITUENTID = [EXPIRATIONS].CONSTITUENTID and
                        (
                            ([PROGRAMS].EXPIRED = 0 and [EXPIRATIONS].EXPIRED = 0)
                            or ([PROGRAMS].EXPIRED = 1)
                        )
                    order by
                        -- Give priority to levels that aren't Inactive (Merge).  Levels set to that status should only be used

                        -- if no other statuses for a constituent exist

                        case 
                            when STATUSCODE <> 3 then 0 -- 3=Inactive (Merge)

                            else 1
                        end asc,
                        [EXPIRATIONS].JOINDATE desc, [EXPIRATIONS].RECOGNITIONLEVELAMOUNT desc
                ) [LEVEL]            
            )