if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_report_GetBlogPostViews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_report_GetBlogPostViews]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE [dbo].cs_report_GetBlogPostViews
(
        @BegReportDate DATETIME
	,@EndReportDate DATETIME
)

AS
BEGIN

-- declare @BegReportDate DateTime
-- set @BegReportDate = '3/6/2006'
-- declare @EndReportDate DateTime
-- set @EndReportDate = '3/7/2006'

-- Most Viewed Blog Posts --
SELECT
	p.PostID, t.TViews, p.Subject
FROM
(
	SELECT TOP 25
		  posts.ThreadID
		,(Select SUM(p2.TotalViews) from cs_Posts p2 where p2.ThreadID = posts.ThreadID AND p2.PostDate between @BegReportDate and @EndReportDate) AS TViews
	FROM
		cs_Sections sections
		INNER JOIN cs_Threads threads ON sections.SectionID = threads.SectionID
	        INNER JOIN cs_Posts posts ON threads.ThreadID = posts.ThreadID
	WHERE
		posts.PostDate between @BegReportDate and @EndReportDate
	        AND sections.ApplicationType = 1
	GROUP BY
		posts.ThreadID
	ORDER BY
		TViews DESC
) AS t
INNER JOIN
	cs_Posts p on t.ThreadID = p.ThreadID
WHERE
	p.PostID = p.ParentID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_report_GetBlogPostViews] to public
go
