Statistics are collected by date: hour: Day: Month: Year.

Create proc [DBO]. tongjixinxi
@ Keywordid int,
@ Type Int = 0 -- 0: Day-4, 8, 12, 16, 20, 24
-- 1: Week-1, 2, 3, 4, 5, 6, 7
-- 2: month-5, 10, 15, 20, 25, Di
-- 3: year-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: Day-4, 8, 12, 16, 20, 24 ---- Note: This is for test use; and other projects are completed; you can delete the formal use of this stored procedure;
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: Week-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 ---- to verify the data to be modified
Group by datename (weekday, T. lastupdateddate) order by datename (weekday, T. lastupdateddate)
ASC
End
Else if (@ type = 2) -- 2: month-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: year-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
Datename (year, T. lastupdateddate) order by datename (year, T. lastupdateddate) ASC
End
End