USP_DATALIST_COMMUNITYMEMBERMOSTFREQUENTPAGES

Lists the Blackbaud Internet Solutions pages most frequently visited by a given community member.
Parameters

Parameter	Parameter Type	Mode	Description
@NETCOMMUNITYCLIENTUSERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@VIEWFILTER	tinyint	IN	View
@MAXPAGES	int	IN	Number
@ASOFDATE	datetime	IN	As of
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_COMMUNITYMEMBERMOSTFREQUENTPAGES
                (
                    @NETCOMMUNITYCLIENTUSERID uniqueidentifier,
                    @VIEWFILTER tinyint = 2,
                    @MAXPAGES int = 5,
                    @ASOFDATE datetime = null
                )
                as begin
                    set nocount on;

                    if @VIEWFILTER is null
                        set @VIEWFILTER = 2;

                    if @MAXPAGES is null or @MAXPAGES < 1 or @MAXPAGES > 500
                        set @MAXPAGES = 5;

                    if @ASOFDATE is null
                        set @ASOFDATE = getdate();

                    declare @STARTDATE datetime;
                    declare @ENDDATE datetime;

                    if @VIEWFILTER = 0
                        begin
                            set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@ASOFDATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@ASOFDATE, 1);
                        end
                    else if @VIEWFILTER = 1
                        begin
                            set @STARTDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@ASOFDATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@ASOFDATE, 1);
                        end
                    else if @VIEWFILTER = 2
                        begin
                            set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@ASOFDATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ASOFDATE, 1);
                        end
                    else if @VIEWFILTER = 3
                        begin
                            set @STARTDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@ASOFDATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@ASOFDATE, 1);
                        end
                    else if @VIEWFILTER = 4
                        begin
                            set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@ASOFDATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@ASOFDATE, 1);
                        end
                    else if @VIEWFILTER = 5
                        begin
                            set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@ASOFDATE, 0);
                            set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@ASOFDATE, 1);
                        end
                    else if @VIEWFILTER = 6
                        begin
                            set @STARTDATE = null;
                            set @ENDDATE = null;
                        end

                    select top (@MAXPAGES)
                        [NETCOMMUNITYWEBTRAFFIC].[PAGENAME],
                        [NETCOMMUNITYWEBTRAFFIC].[URL],
                        count([NETCOMMUNITYWEBTRAFFIC].[ID]) [VISITCOUNT]
                    from
                        dbo.NETCOMMUNITYWEBTRAFFIC
                    where
                        [NETCOMMUNITYWEBTRAFFIC].[NETCOMMUNITYCLIENTUSERID] = @NETCOMMUNITYCLIENTUSERID
                        and
                        (
                            @STARTDATE is null
                            or @ENDDATE is null
                            or [NETCOMMUNITYWEBTRAFFIC].[REQUESTDATE] between @STARTDATE and @ENDDATE
                        )
                    group by
                        [NETCOMMUNITYWEBTRAFFIC].[PAGENAME],
                        [NETCOMMUNITYWEBTRAFFIC].[URL]
                    order by
                        [VISITCOUNT] desc, [NETCOMMUNITYWEBTRAFFIC].[PAGENAME];

                    return 0;
                end