UFN_CONSTITUENTRECOGNITION_GETCOUNT_BYDATERANGE

Returns a count of recognitions for a particular program and date range.
Return

Return Type
int
Parameters

Parameter	Parameter Type	Mode
@PROGRAMID	uniqueidentifier	IN
@SORTID	uniqueidentifier	IN
@GROUPBY	tinyint	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
Definition

Copy


            create function dbo.UFN_CONSTITUENTRECOGNITION_GETCOUNT_BYDATERANGE(
                @PROGRAMID uniqueidentifier,                
                @SORTID uniqueidentifier,
                @GROUPBY tinyint,
                @STARTDATE datetime,
                @ENDDATE datetime
            )
            returns int
            as
            begin
                -- NOTE:  This function is based on UFN_CONSTITUENTRECOGNITION_GETCOUNT_BYASOFDATE


                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
                set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
                declare @VALUE int;
                set @VALUE = 0;

                -- Get program-related revenue

                with CONSTITRECS_CTE as (
                    select CR.ID, CR.CONSTITUENTID, CR.RECOGNITIONLEVELID, CR.RECOGNITIONPROGRAMID, CR.STATUSCODE
                    from dbo.CONSTITUENTRECOGNITION CR
                    where CR.JOINDATE = (select max(CR2.JOINDATE) from dbo.CONSTITUENTRECOGNITION CR2
                                            where CR2.CONSTITUENTID = CR.CONSTITUENTID
                                                and CR2.RECOGNITIONPROGRAMID = @PROGRAMID
                                                and CR2.JOINDATE >= @STARTDATE
                                                and CR2.JOINDATE <= @ENDDATE
                                                and ((CR2.EXPIRATIONDATE >= @ENDDATE) or (CR.EXPIRATIONDATE is null)))
                    )    

                select
                    @VALUE = count(distinct CR.ID)
                from
                    CONSTITRECS_CTE as CR
                    inner join dbo.RECOGNITIONLEVEL as RL on CR.RECOGNITIONLEVELID = RL.ID
                    inner join dbo.RECOGNITIONPROGRAM as RP on CR.RECOGNITIONPROGRAMID = RP.ID
                where
                    CR.RECOGNITIONPROGRAMID = @PROGRAMID                    
                    and
                    (
                        (    @GROUPBY = 1
                            and
                            CR.RECOGNITIONLEVELID = @SORTID
                        )
                        or
                        (    @GROUPBY = 0 and
                            (
                                (@SORTID = RL.TIERCODEID and @SORTID is not null) 
                                or 
                                (@SORTID is null and RL.TIERCODEID is null)
                            )    
                        )
                    )
                    and
                    CR.STATUSCODE <> 1;    

                    return @value;
                end