Stored Procedure Properties

Name	Value
Owner	dbo
Created	2004-05-31
Startup	False
Encrypted	False

Creation Options

Name	Value
QUOTED_IDENTIFIER	OFF
ANSI_NULLS	ON

Parameters

Name	DataType	Length	Type
@DaysBack	int	4	INPUT
@SumDayCount	bit	1	INPUT

Total: 2 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE  procedure Reports_UserVisitsByDay
(
	@DaysBack int,
	@SumDayCount bit
)
AS
DECLARE @UserCount int
DECLARE @PostCount int
DECLARE @AspNetTeamPostCount int
DECLARE @AveragePostPerUser decimal(5,2)
DECLARE @PercentagePostsAspNetTeam decimal(5,2)
DECLARE @ForumStartDate datetime

IF (@DaysBack > 0)
BEGIN
	SELECT TOP 1 @ForumStartDate = PostDate FROM Posts ORDER BY PostDate

	SET @DaysBack = DATEDIFF(d, GetDate(), @ForumStartDate)
END

-- Create a temporary table to insert results into
CREATE Table #UserVisitsByDay (
	StatDate datetime,
	UserCount int,
	PostCount int,
	AvgPostPerUser decimal(5,2),
	PostCountAspNetTeam int,
	PercentagePostsAspNetTeam decimal(5,2)
)


-- Do for each day
WHILE (@DaysBack <= 0)
BEGIN
	IF (@SumDayCount = 1) AND (@DaysBack < -1)
	BEGIN
		-- Users visited in last day
		SELECT @UserCount = Count(*) FROM Users WHERE DatePart(dy, LastActivity) >= DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, LastActivity) >= DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))
		
		-- Users posted in last day
		SELECT @PostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) >= DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) >= DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))

                -- ASPNet Team post count
		SELECT @AspNetTeamPostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) >= DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) >= DatePart(yy, DateAdd(dy, @DaysBack, GetDate())) AND Username IN (SELECT Username FROM UsersInRoles WHERE RoleName = 'AspNetTeam')
	END
	ELSE
	BEGIN
		-- Users visited in last day
		SELECT @UserCount = Count(*) FROM Users WHERE DatePart(dy, LastActivity) = DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, LastActivity) = DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))
		
		-- Users posted in last day
		SELECT @PostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) = DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) = DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))

                -- ASPNet Team post count
		SELECT @AspNetTeamPostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) = DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) = DatePart(yy, DateAdd(dy, @DaysBack, GetDate())) AND Username IN (SELECT Username FROM UsersInRoles WHERE RoleName = 'AspNetTeam')
	END
	
	-- Calculate avg. post/user
	SET @AveragePostPerUser = CAST(@PostCount as decimal) / CAST(@UserCount as decimal)

	-- Calculate avg. post/user from ASP.NET Team
	SET @PercentagePostsAspNetTeam = CAST(@AspNetTeamPostCount as decimal) / CAST(@PostCount as decimal)

	INSERT INTO 
		#UserVisitsByDay 
	VALUES (
		DATEADD(d, @DaysBack, GetDate()), 
		@UserCount, 
		@PostCount, 
		@AveragePostPerUser,
		@AspNetTeamPostCount,
		@PercentagePostsAspNetTeam
		)

	SET @DaysBack = @DaysBack + 1
END


SELECT 
	StatDate,
	UserCount,
	PostCount,
	AvgPostPerUser,
	PostCountAspNetTeam,
	PercentagePostsAspNetTeam
FROM
	#UserVisitsByDay
ORDER BY
	StatDate DESC





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO