UFN_MAJORGIVING_DAYSTOACCEPTEDASK

Returns a result set with #Asks and #Days for each defined Opportunity amount range.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@PROSPECTPLANTYPECODEID	uniqueidentifier	IN
@DESIGNATIONID	uniqueidentifier	IN
@FUNDRAISERID	uniqueidentifier	IN

Definition

Copy



            create function dbo.UFN_MAJORGIVING_DAYSTOACCEPTEDASK(
                @STARTDATE datetime,
                @ENDDATE datetime,
                @PROSPECTPLANTYPECODEID uniqueidentifier,
                @DESIGNATIONID uniqueidentifier,
                @FUNDRAISERID uniqueidentifier
            )
            returns table
            as

            return

                select 
                    R.ID OPPORTUNITYAMOUNTBRACKETID,
                    R.NAME,
                    R.LOWERLIMIT,
                    count(*) ASKS,
                    sum(datediff(day, I.FIRSTDATE, O.RESPONSEDATE)) DAYS
                from 
                    dbo.OPPORTUNITY O
                    inner join dbo.PROSPECTPLAN PP on PP.ID=O.PROSPECTPLANID
                    inner join dbo.UFN_OPPORTUNITYAMOUNTRANGES() R on O.AMOUNT between R.LOWERLIMIT and R.UPPERLIMIT
                    inner join (
                        select min(DATE) FIRSTDATE, PROSPECTPLANID 
                        from dbo.INTERACTION 
                        group by PROSPECTPLANID
                    ) I on I.PROSPECTPLANID=PP.ID
                where
                    O.STATUSCODE=3
                    and O.RESPONSEDATE between @STARTDATE and @ENDDATE
                    and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                    and (@FUNDRAISERID is null or PP.PRIMARYMANAGERFUNDRAISERID = @FUNDRAISERID)
                    and (@DESIGNATIONID is null or exists(select 1 from dbo.OPPORTUNITYDESIGNATION OD where OD.OPPORTUNITYID=O.ID and OD.DESIGNATIONID=@DESIGNATIONID))
                group by
                    R.ID,
                    R.NAME,
                    R.LOWERLIMIT;