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