cs_blogactivitynightlyjob.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE    PROCEDURE [dbo].cs_BlogActivityNightlyJob

AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
	declare @RunDate datetime
	declare @JobRunDate datetime
	set @JobRunDate = getdate()
	set @RunDate = dateadd(d, -1, convert(datetime, (convert(varchar(10), @JobRunDate, 101))))
	 
        insert into [cs_BlogActivityReport] 
	select t.SectionID, t.ThreadID, @RunDate as CoverageDate
	 ,TotalViews  = (select t1.TotalViews from dbo.cs_Threads t1 left outer join cs_BlogActivityReport yesterdays on t.ThreadID = yesterdays.ThreadID and yesterdays.CoverageDate = dateadd(d, -1, @RunDate) where t1.ThreadID = t.ThreadID and t1.SectionID = t.SectionID)
	 ,DayViews = (select coalesce((t1.TotalViews - yesterdays.TotalViews), t1.TotalViews) from dbo.cs_Threads t1 left outer join cs_BlogActivityReport yesterdays on t.ThreadID = yesterdays.ThreadID and yesterdays.CoverageDate = dateadd(d, -1, @RunDate) where t1.ThreadID = t.ThreadID and t1.SectionID = t.SectionID)
	 ,DayComments  = (select count(*) from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID <> p1.ParentID and p1.ApplicationPostType = 4 and p1.PostDate between @RunDate and dateadd(d, 1, @RunDate))
	 ,DayTrackBacks = (select count(*) from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID <> p1.ParentID and p1.ApplicationPostType = 8 and p1.PostDate between @RunDate and dateadd(d, 1, @RunDate))
         ,IsPost   = (select case when p1.ApplicationPostType = 1 then 1 else 0 end from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID)
	 ,IsArticle  = (select case when p1.ApplicationPostType = 2 then 1 else 0 end from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID)
	 ,JobExecutionTimeStamp = @JobRunDate
         ,DayPosts = (select count(*) from dbo.cs_Posts p1 where p1.ApplicationPostType = 1 and p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID and p1.PostDate between @RunDate and dateadd(d, 1, @RunDate))
	 ,DayArticles = (select count(*) from dbo.cs_Posts p1 where p1.ApplicationPostType = 2 and p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID and p1.PostDate between @RunDate and dateadd(d, 1, @RunDate))
	from dbo.cs_Threads t 
	-- Some existing CS databases have orphaned thread records that should have been deleted, so check to make sure at least one top-level post exists for the thread
	where exists(select PostID from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID)

END






GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_BlogActivityNightlyJob to public
go