分别按照 日期：时： 天：月：年进行统计信息

CREATE  PROC [dbo].tongjixinxi
   @KeywordId int,
   @Type int=0 --0:日-4,8,12,16,20,24
    --1:周-1,2,3,4,5,6,7
    --2:月-5,10,15,20,25,di
    --3:年-3,,6,9,12

   --@TotalInfo int output
AS
BEGIN
 DECLARE @PageTemp1 table
    (
        Pid Int,
        KeywordId INT,
     Title varchar(255),
  Brief varchar(255),
  Link varchar(255),
  CreatedDate datetime,
  LastUpdatedDate datetime 
    )
    DECLARE @PageTemp2 table
    (
        Pid Int,
        KeywordId INT,
     Title varchar(255),
  Brief varchar(255),
  Link varchar(255),
  CreatedDate datetime,
  LastUpdatedDate datetime 
    )
    INSERT INTO @PageTemp1  SELECT P.Id,T.KeywordId,T.Title,P.Brief,P.Link,P.CreatedDate,P.LastUpdatedDate FROM PageSnapTitles T inner JOIN PageSnaps P  ON T.Id=P.TitleId where T.KeywordId=@KeywordId
  IF(@Type=0) --0:日-4,8,12,16,20,24----NOTE:此处为测试使用；等项目完成；正式使用该存储过程可删去；
  BEGIN
   DECLARE @num INT
   SELECT  @num=COUNT(*) FROM PageSnaps WHERE DAY(LastUpdatedDate) =DAY(GETDATE())-1
     IF(@num>3)
     BEGIN
      SELECT DATEPART(HOUR,T.LastUpdatedDate) AS 'Date',COUNT(T.Pid) AS PsCount FROM
      (SELECT * FROM @PageTemp1 WHERE DATEPART(HOUR,LastUpdatedDate)
      BETWEEN DATEPART(HOUR,GETDATE()) AND DATEPART(HOUR,GETDATE())-24) AS T
      GROUP BY DATEPART(HOUR,LastUpdatedDate)
      ORDER BY DATEPART(HOUR,LastUpdatedDate) asc
     END
     ELSE
     BEGIN
        SELECT DATEPART(HOUR,LastUpdatedDate) AS 'Date',COUNT(*) AS PsCount FROM @PageTemp1 GROUP BY DATEPART(HOUR,LastUpdatedDate) ORDER BY DATEPART(HOUR,LastUpdatedDate) asc
     END
  END
  ELSE IF(@Type=1)-----1:周-1,2,3,4,5,6,7
  BEGIN
  select datename(weekday,T.LastUpdatedDate)AS 'Date',COUNT(T.Pid) AS PsCount
   from (SELECT * FROM @PageTemp1 WHERE  DATEPART(WEEK,LastUpdatedDate)
   BETWEEN DATEPART(WEEK,GETDATE()) AND DATEPART(WEEK,GETDATE())+1) AS T ----为了验证数据有待修改
   GROUP BY DATENAME(weekday,T.LastUpdatedDate) ORDER BY datename(weekday,T.LastUpdatedDate)
   ASC
  END
     ELSE IF(@Type=2)--2:月-5,10,15,20,25,di
  BEGIN
   SELECT convert(varchar(10),LastUpdatedDate,120) as 'Date', count(Pid) as PsCount from @PageTemp1
   group by convert(varchar(10),LastUpdatedDate,120)
   order By convert(varchar(10),LastUpdatedDate,120) ASC
  END
  ELSE IF(@Type=3)--3:年-3,,6,9,12
  BEGIN
    SELECT DATENAME(YEAR,T.LastUpdatedDate) AS 'Date',COUNT(T.Pid) AS PsCount FROM
    (SELECT *FROM @PageTemp1 WHERE YEAR(LastUpdatedDate)
    BETWEEN YEAR(DATEADD(YEAR,-10,getdate())) AND YEAR(getdate()))AS T GROUP BY 
    DATENAME(YEAR,T.LastUpdatedDate) ORDER BY  DATENAME(YEAR,T.LastUpdatedDate) ASC   
  END
 END

转载于:https://www.cnblogs.com/qiliping/archive/2011/07/23/2114945.html