UFN_CONSTITUENT_GETCURRENTRECOGNITIONS

create function dbo.UFN_CONSTITUENT_GETCURRENTRECOGNITIONS
            (
                @CURRENTDATE datetime,
                @RECOGNITIONPROGRAMID uniqueidentifier
            )
            returns table
            as
            return
            (
                with 
                [PROGRAMRECOGNITIONS] as 
                (
                    select 
                        case when max(EXPIRATIONDATE) is null or max(EXPIRATIONDATE) >= @CURRENTDATE then 0 else 1 end [EXPIRED],
                        CONSTITUENTID
                    from dbo.CONSTITUENTRECOGNITION
                    where CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
                    group by CONSTITUENTID
                ),
                [RECOGNITIONLEVELS] as
                (
                    select 
                        CONSTITUENTRECOGNITION.ID,
                        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
                    where CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
                )
                select 
                    TOPLEVEL.ID,
                    PROGRAMRECOGNITIONS.EXPIRED,
                    PROGRAMRECOGNITIONS.CONSTITUENTID
                from [PROGRAMRECOGNITIONS]
                cross apply 
                (
                    select top 1 [RECOGNITIONLEVELS].ID
                    from [RECOGNITIONLEVELS]
                    where                 
                        [PROGRAMRECOGNITIONS].CONSTITUENTID = [RECOGNITIONLEVELS].CONSTITUENTID and
                        (
                            ([PROGRAMRECOGNITIONS].EXPIRED = 0 and [RECOGNITIONLEVELS].EXPIRED = 0)
                            or ([PROGRAMRECOGNITIONS].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,
                        [RECOGNITIONLEVELS].JOINDATE desc, [RECOGNITIONLEVELS].RECOGNITIONLEVELAMOUNT desc
                ) [TOPLEVEL]            
            )