spStats_UpdateStatistics
Definition
Copy
CREATE PROCEDURE [dbo].[spStats_UpdateStatistics]
AS
BEGIN
set nocount on
declare @currentdate datetime
declare @updatedate datetime
set @currentdate = getutcdate()
select @updatedate = updatedate from serverstatus where id = 3
if @@rowcount = 0
BEGIN
--This is the first time we have run this
--There shouldn't be any rows in the stats table
--but we will do a delete all just in case
delete from StatisticsDayPage
delete from StatisticsDaySite
delete from StatisticsMonthPage
delete from StatisticsMonthSite
insert into serverstatus(id,updatedate) values(3,getutcdate())
END
else if @updatedate is null
BEGIN
--This shouldn't generally happen to be the case
delete from StatisticsDayPage
delete from StatisticsDaySite
delete from StatisticsMonthPage
delete from StatisticsMonthSite
END
else if datediff(mi,@updatedate, @currentdate) <= 60
--we have run this before...don't do it again if it has been less than an hour...
return
else
BEGIN
--clear out partial records...
delete from StatisticsDayPage
where YYYYMMDD = CONVERT(nchar(8),@updatedate,112)
delete from StatisticsDaySite
where YYYYMMDD = CONVERT(nchar(8),@updatedate,112)
delete from StatisticsMonthPage
where YYYYMM = CONVERT(nchar(6),@updatedate,112)
delete from StatisticsMonthSite
where YYYYMM = CONVERT(nchar(6),@updatedate,112)
END
declare @maxdate datetime
declare @YYYYMMDD as nchar(8)
declare @YYYYMM as nchar(6)
select @YYYYMMDD = max(YYYYMMDD) from StatisticsDayPage
if (@YYYYMMDD is null)
set @maxdate = '19711006'
else
set @maxdate = dateadd(d,1,@YYYYMMDD)
INSERT INTO [dbo].[StatisticsDayPage] (YYYYMMDD, PageID, PageViews, Visits, Hosts, Users, UserPageViews, UserVisits)
SELECT convert(nchar(8),requestdate,112) YYYYMMDD, PageID,
COUNT(*), COUNT(DISTINCT guid), COUNT(DISTINCT TCPIPADDRESS),
count(distinct (case when userid>0 then userid else null end)),
count(distinct (case when userid>0 then statsid else null end)),
count(distinct (case when userid>0 then guid else null end))
from [dbo].[stats]
where requestdate >= @maxdate
group by convert(nchar(8),requestdate,112), PageID
select @YYYYMM = max(YYYYMM) from StatisticsMonthPage
if (@YYYYMM is null)
set @maxdate = '19711006'
else
set @maxdate = dateadd(m,1,@YYYYMM+'01')
INSERT INTO [dbo].[StatisticsMonthPage] (YYYYMM, PageID, PageViews, Visits, Hosts, Users, UserPageViews, UserVisits)
SELECT convert(nchar(6),requestdate,112) YYYYMM, PageID,
COUNT(*), COUNT(DISTINCT guid), COUNT(DISTINCT TCPIPADDRESS),
count(distinct (case when userid>0 then userid else null end)),
count(distinct (case when userid>0 then statsid else null end)),
count(distinct (case when userid>0 then guid else null end))
from [dbo].[stats]
where requestdate >= @maxdate
group by convert(nchar(6),requestdate,112), PageID
select @maxdate = max(YYYYMMDD) from StatisticsDaySite
if (@maxdate is null)
set @maxdate = '19711006'
else
set @maxdate = dateadd(d,1, @maxdate)
INSERT INTO [dbo].[StatisticsDaySite] (YYYYMMDD, PageViews, Visits, Hosts, Users, UserPageViews, UserVisits, SessionTime, OnePageVisits)
SELECT convert(nchar(8),requestdate,112) YYYYMMDD,
COUNT(*), COUNT(DISTINCT guid), COUNT(DISTINCT TCPIPADDRESS),
count(distinct (case when userid>0 then userid else null end)),
count(distinct (case when userid>0 then statsid else null end)),
count(distinct (case when userid>0 then guid else null end)), 0, 0
from [dbo].[stats]
where requestdate >= @maxdate
group by convert(nchar(8),requestdate,112)
UPDATE StatisticsDaySite
set SessionTime = TimeTable.SessionTime,
OnePageVisits = TimeTable.OnePageVisits
from StatisticsDaySite sds
inner join (
SELECT
sessions.YYYYMMDD YYYYMMDD,
convert(bigint,sum(sessions.sessiontime)) SessionTime,
sum((case when sessions.sessiontime=0 then 1 else 0 end)) OnePageVisits
from (
SELECT convert(nchar(8),requestdate,112) YYYYMMDD, guid,
convert(bigint,datediff(s,min(requestdate),max(requestdate))) sessiontime
from [dbo].[stats]
where requestdate >= @maxdate
group by convert(nchar(8),requestdate,112), guid
) sessions
group by YYYYMMDD
) TimeTable
on TimeTable.YYYYMMDD = sds.YYYYMMDD
select @YYYYMM = max(YYYYMM) from StatisticsMonthSite
if (@YYYYMM is null)
set @maxdate = '19711006'
else
set @maxdate = dateadd(m,1,@YYYYMM+'01')
INSERT INTO [dbo].[StatisticsMonthSite] (YYYYMM, PageViews, Visits, Hosts, Users, UserPageViews, UserVisits, SessionTime, OnePageVisits)
SELECT convert(nchar(6),requestdate,112) YYYYMM,
COUNT(*), COUNT(DISTINCT guid), COUNT(DISTINCT TCPIPADDRESS),
count(distinct (case when userid>0 then userid else null end)),
count(distinct (case when userid>0 then statsid else null end)),
count(distinct (case when userid>0 then guid else null end)), 0, 0
from [dbo].[stats]
where requestdate >= @maxdate
group by convert(nchar(6),requestdate,112)
UPDATE StatisticsMonthSite
set SessionTime = TimeTable.SessionTime,
OnePageVisits = TimeTable.OnePageVisits
from StatisticsMonthSite sms
inner join (
SELECT
sessions.YYYYMM YYYYMM,
convert(bigint,sum(sessions.sessiontime)) SessionTime,
sum((case when sessions.sessiontime=0 then 1 else 0 end)) OnePageVisits
from (
SELECT convert(nchar(6),requestdate,112) YYYYMM, guid,
convert(bigint,datediff(s,min(requestdate),max(requestdate))) sessiontime
from [dbo].[stats]
where requestdate >= @maxdate
group by convert(nchar(6),requestdate,112), guid
) sessions
group by YYYYMM
) TimeTable
on TimeTable.YYYYMM = sms.YYYYMM
-- =============================== EMAIL Stats =================================
-- This section added to populate email stats like page hits etc.
-- Calculating email stats on the fly was too expensive with bigger databases
-- and timed out
DECLARE @EmailStatsTable table
(
EmailID int,
Sent int,
Opened int,
Bounced int,
Pagehits int
)
-- 1. Get all email stats, load into temp table, some may be new, some
-- columns may have changed (bounced, pagehits etc.)
INSERT INTO @EmailStatsTable (EmailID, Sent, Opened, Bounced, Pagehits)
SELECT
ej.EMailID,
count(distinct(z.ID)),
ISNULL(sum(z.Opened),0),
ISNULL(sum(z.DSNed),0),
ISNULL(sum(z.Hits),0)
FROM (SELECT emailID FROM dbo.EmailJob WHERE ConditionalParentJobID Is Null) ej
LEFT OUTER JOIN
(SELECT
ejr.ID,
max(ejr.EmailID) as EmailID,
max(cast(ejr.opened as int)) as Opened,
max(cast(ejr.DSNed as int)) as DSNed,
Count(stats.statsID) as Hits
FROM EmailJob_Recipient ejr
LEFT OUTER JOIN dbo.stats stats on ejr.ID = stats.EmailJobRecipientID
group by ejr.ID) z
on ej.EmailID = z.EmailID
group by ej.EmailID
-- 2.Update existing Email_Stats rows
-- columns may have changed (bounced, pagehits etc.)
UPDATE dbo.[Email_Stats]
SET
dbo.[Email_Stats].Sent = es.Sent,
dbo.[Email_Stats].Opened = es.Opened,
dbo.[Email_Stats].Bounced = es.Bounced,
dbo.[Email_Stats].Pagehits = es.Pagehits
FROM
@EmailStatsTable es, dbo.[Email_Stats] ems
WHERE
es.EmailID = ems.ID
-- 3. Load new rows into Email_Stats table
INSERT INTO dbo.[Email_Stats]
SELECT
* from @EmailStatsTable est
WHERE
est.EmailID not in (Select ID from dbo.[Email_Stats])
-- =============================== End EMAIL Stats ===============================
-- ========================== Content Comparison Stats ============================
--
--
DECLARE @CCStatsTable table(PageID int, Clickthroughs int, CCPRelID int)
INSERT INTO @CCStatsTable(PageID, Clickthroughs, CCPRelID)
SELECT s.PageID,
count(s.PageID) as Clickthroughs,
cc.ID
FROM
[dbo].ContentComparisons cc
INNER JOIN [dbo].[Stats] s ON s.CCPRelID = cc.ID
INNER JOIN [dbo].ContentComparisonPart ccp ON cc.CCPID = ccp.ID
GROUP BY cc.ID, s.PageID
--Update the clickthrough counts for any existing parts
UPDATE [dbo].ContentComparisonStats
SET
[dbo].ContentComparisonStats.Clickthroughs = t.Clickthroughs
FROM
@CCStatsTable t, [dbo].ContentComparisonStats
WHERE
[dbo].ContentComparisonStats.CCPRelID = t.CCPRelID
AND [dbo].ContentComparisonStats.PageID = t.PageID
--For new parts, insert
INSERT INTO [dbo].ContentComparisonStats(CCPRelID, Clickthroughs, PageID)
SELECT
t.CCPRelID,
t.Clickthroughs,
t.PageID
FROM
@CCStatsTable t
WHERE
NOT EXISTS (SELECT * FROM [dbo].ContentComparisonStats ccs WHERE t.CCPRelID=ccs.CCPRelID AND t.PageID=ccs.PageID)
-- ======================== End Content Comparison Stats ==========================
update serverstatus
set updatedate = @currentdate
where id = 3
END