USP_DATALIST_CONSTITUENTRECOGNITIONHISTORY

A list of recognition levels over time for a constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTRECOGNITIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_CONSTITUENTRECOGNITIONHISTORY(@CONSTITUENTRECOGNITIONID uniqueidentifier)
            as
                set nocount on;

                declare @RECOGNITIONPROGRAMID uniqueidentifier;
                declare @CONSTITUENTID uniqueidentifier;
                declare @EXPIRESONCODE tinyint;

                select @EXPIRESONCODE = RP.EXPIRESONCODE,
                    @CONSTITUENTID = CONSTITUENTID,
                    @RECOGNITIONPROGRAMID = RECOGNITIONPROGRAMID
                from dbo.CONSTITUENTRECOGNITION CR
                inner join dbo.RECOGNITIONPROGRAM RP on CR.RECOGNITIONPROGRAMID = RP.ID
                where CR.ID = @CONSTITUENTRECOGNITIONID

                select CONSTITUENTRECOGNITION.ID,
                    case when @EXPIRESONCODE = 0 then
                            convert(nvarchar(4), year(dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(JOINDATE,0)))
                        else
                            convert(nvarchar(4), year(dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(JOINDATE,0))) + ' - ' + convert(nvarchar(4), year(dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(JOINDATE,1)))
                        end as [YEAR],
                       RECOGNITIONLEVEL.NAME as RECOGNITIONLEVEL,
                       CONSTITUENTRECOGNITION.JOINDATE,
                       CONSTITUENTRECOGNITION.TOTALAMOUNT,
                       CONSTITUENTRECOGNITION.COMMENTS,
                       CONSTITUENTRECOGNITION.ISANONYMOUS,
                       CONSTITUENTRECOGNITION.BASECURRENCYID,
                       CONSTITUENTRECOGNITION.STATUS
                from dbo.CONSTITUENTRECOGNITION
                inner join dbo.RECOGNITIONLEVEL on CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = RECOGNITIONLEVEL.ID
                where CONSTITUENTRECOGNITION.CONSTITUENTID = @CONSTITUENTID 
                    and CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
                order by JOINDATE desc, RECOGNITIONLEVEL.AMOUNT desc;