Site Activity and VoteGraph

50

(click on this box to dismiss)

Plot the site activity by voting, questions, answers and comments, aggregated per week.

Q&A for Joomla! administrators, users, developers and designers

``````with Activity as (
select DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate) as YearMon,
case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name as Series,
count(*) as Action
from Posts p,
VoteTypes vt
where p.Id = v.PostId
and v.VoteTypeId = vt.Id
group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate),
case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name
UNION
select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon,
pt.Name as Series,
count(*) as Action
from Posts p, PostTypes pt
where p.PostTypeId in (1, 2)
and p.PostTypeId = pt.Id
Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)),
pt.Name
UNION
SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) as YearMon,
'Comment' as Series,
count(*) as Action
Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate))
)
select *
from Activity
pivot (
sum(Action)
as Pivoted
where YearMon >= DATEADD(WEEK, -60, CURRENT_TIMESTAMP) -- previous 60 weeks.
and YearMon < CURRENT_TIMESTAMP -- ignore most recent week.
order by YearMon``````

Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached