cs_BlogActivityNightlyJob//cs_BlogActivityReportAggregate_Get///cs_BlogActivityReportRecords_Get

ALTER     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))))
         /**//*101---美国---输入输出(mm/dd/yyyy)*/
         /**//*先转换成varchar(10),然后再转换成datetime,并在天数上递增-1*/
     
        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)
        /**//*就是插入数据,博客这块放着以后再来.在主要的GROUP\SECTION\THREAD\POST表中同时保持博客,论坛,相册的数据.*/
END







GO

cs_BlogActivityReportAggregate_Get
ALTER       PROCEDURE [dbo].cs_BlogActivityReportAggregate_Get
( ----总计
     @nRecordNumberStart INT
        ,@nRecordNumberEnd INT
        ,@BegReportDate DateTime
    ,@EndReportDate DateTime
    ,@Paged BIT
)

AS
BEGIN

-- declare @Paged BIT
-- set @Paged = 1
-- declare @EndReportDate DateTime
-- set @EndReportDate = '1/1/2008'
-- declare @BegReportDate DateTime
-- set @BegReportDate = '1/1/1900'
-- declare @nRecordNumberStart INT
-- set @nRecordNumberStart = 1
-- declare @nRecordNumberEnd INT
-- set @nRecordNumberEnd = 100


IF @Paged = 1
    BEGIN
        DECLARE @totalRecords INT
        --------------------------------------------------------------------
        -- Define the table to do the filtering and paging(过滤和页面调度)
        --------------------------------------------------------------------
        DECLARE @tblTempData TABLE
        (
            nID INT IDENTITY
            ,DayViews INT
            ,DayComments INT
            ,DayTrackbacks INT
            ,DayPosts INT
            ,DayArticles INT
            ,ApplicationKey varchar(100)
        )
        INSERT INTO @tblTempData
        (
            DayViews
            ,DayComments
            ,DayTrackbacks
            ,DayPosts
            ,DayArticles
            ,ApplicationKey
        )
               /**//*创建临时表,插入数据*/
        SELECT
            SUM(DayViews) AS DayViews, SUM(DayComments) AS DayComments, SUM(DayTrackBacks) AS DayTrackBacks
            ,SUM(DayPosts) AS DayPosts, SUM(DayArticles) AS DayArticles
            ,(SELECT  ApplicationKey FROM cs_Sections AS s WHERE  (SectionID = bar.SectionID)) AS ApplicationKey
        FROM
            cs_BlogActivityReport AS bar
            INNER JOIN cs_Sections sec ON sec.SectionID = bar.SectionID
        WHERE 
            ApplicationType = 1
                AND bar.CoverageDate between @BegReportDate and @EndReportDate
            AND DayViews > 0
        GROUP BY
            bar.SectionID
        ORDER BY
            DayViews DESC
                  /**//*从cs_BlogActivityReport表中统计数据插入临时表中*/
        SET @totalRecords = @@rowcount
        -------------------------------------------------------------------------------------
        
        SELECT
            DayViews
            ,DayComments
            ,DayTrackbacks
            ,DayPosts
            ,DayArticles
            ,ApplicationKey
        FROM
            @tblTempData 
        WHERE
            nID BETWEEN @nRecordNumberStart AND @nRecordNumberEnd
        ORDER BY 
            nID ASC
        /**//*从临时表查询数据*/
        --Return Record Count
                --返回总记录
        SELECT @totalRecords

        --Return TotalBlogs
                 --返回总博客
        SELECT COUNT(SectionID)
        FROM cs_Sections
        WHERE ApplicationType = 1

        --Return TotalEnabledBlogs
                --返回总可用博客
        SELECT COUNT(SectionID)
        FROM cs_Sections
        WHERE ApplicationType = 1 AND IsActive = 1

        --Return TotalDisabledBlogs
                --返回总不可用博客(不活动)
        SELECT COUNT(SectionID)
        FROM cs_Sections
        WHERE ApplicationType = 1 AND IsActive = 0
    END
ELSE
    BEGIN  --如果无页面控制
        SELECT
            (SELECT  ApplicationKey FROM cs_Sections AS s WHERE  (SectionID = bar.SectionID)) AS ApplicationKey
            ,SUM(DayViews) As TotalViews
            ,SUM(DayComments) As TotalComments
            ,SUM(DayTrackBacks) As TotalTrackBacks
            ,SUM(DayPosts) As TotalPosts
            ,SUM(DayArticles) As TotalArticles
        FROM
            cs_BlogActivityReport AS bar
            INNER JOIN cs_Sections sec ON sec.SectionID = bar.SectionID
        WHERE
            ApplicationType = 1
                AND bar.CoverageDate between @BegReportDate and @EndReportDate
        GROUP BY
            bar.SectionID
        ORDER BY
            TotalViews DESC
    END
END                                                                                                                                                                                                                               



GO

cs_BlogActivityReportRecords_Get
ALTER   PROCEDURE [dbo].cs_BlogActivityReportRecords_Get
(
     @nRecordNumberStart INT
        ,@nRecordNumberEnd INT
        ,@BegReportDate DateTime
    ,@EndReportDate DateTime
)

AS
BEGIN
    DECLARE @totalRecords INT

    --------------------------------------------------------------------
        -- Define the table to do the filtering and paging
        --------------------------------------------------------------------
          /**//*创建临时表并添加数据*/
        DECLARE @tblTempData TABLE
        (
            nID INT IDENTITY
            ,SectionID INT
            ,ThreadID INT
        ,CoverageDate DateTime
            ,TotalViews INT
        ,DayViews INT
        ,DayComments INT
        ,DayTrackbacks INT
        ,IsPost bit
        ,IsArticle bit
        ,JobExecutionTimeStamp DateTime
        ,PostID INT
        ,PostSubject varchar(256)
        ,ApplicationPostType INT
        ,ApplicationKey varchar(100)
        )
    INSERT INTO @tblTempData
    (
            SectionID
            ,ThreadID
        ,CoverageDate
            ,TotalViews
        ,DayViews
        ,DayComments
        ,DayTrackbacks
        ,IsPost
        ,IsArticle
        ,JobExecutionTimeStamp
        ,PostID
        ,PostSubject
        ,ApplicationPostType
        ,ApplicationKey
        )
        SELECT  SectionID, ThreadID, CoverageDate, TotalViews, DayViews, DayComments, DayTrackBacks, IsPost, IsArticle, JobExecutionTimeStamp,
            (SELECT  PostID FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) AS PostID,
        (SELECT  Subject FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) AS PostSubject,
            (SELECT  ApplicationPostType FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) AS ApplicationPostType,
            (SELECT  ApplicationKey FROM     cs_Sections AS s WHERE  (SectionID = bar.SectionID)) AS ApplicationKey
    FROM     cs_BlogActivityReport AS bar
    WHERE 
        (((SELECT  ApplicationPostType FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) = 1)
                OR ((SELECT  ApplicationPostType FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) = 2))
                AND bar.CoverageDate between @BegReportDate and @EndReportDate
    ORDER BY
        bar.CoverageDate
    SET @totalRecords = @@rowcount
        ---------------------------------------------------------------------------------------------------------------------------------------
          /**//*查询数据*/
    SELECT
            SectionID
            ,ThreadID
            ,TotalViews
        ,DayViews
        ,DayComments
        ,DayTrackbacks
        ,IsPost
        ,IsArticle
        ,JobExecutionTimeStamp
        ,PostID
        ,PostSubject
        ,ApplicationPostType
        ,ApplicationKey
        ,CoverageDate
        FROM
        @tblTempData 
        WHERE
        nID BETWEEN @nRecordNumberStart AND @nRecordNumberEnd
        ORDER BY 
            nID ASC

-- Return totalRecords
SELECT @totalRecords

END
                                                                                                                                                                                                                                         



GO

转载于:https://www.cnblogs.com/ruanbl/archive/2006/09/04/494159.html