0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

;with TagsOnPost as (
select postid
from posttags pt
inner join tags t on t.id = pt.tagid
where t.tagname in ('%##TagName##%')
)
select hour
, sum([cre]) as [Create]
, sum(edit) as [Edit]
, sum(activity) as [Active]
, sum(comments) as [Comments]
from
(
select datepart(hh, creationdate) as hour
, count(*) as [cre]
, 0 as edit
, 0 as activity
, 0 as comments
from posts p 
inner join TagsOnPost ph on ph.postid = p.id
group by datepart(hh, creationdate)
union 
select datepart(hh, lasteditdate) 
, 0
, count(*)
, 0
, 0
from posts p 
inner join TagsOnPost ph on ph.postid = p.id
and lasteditdate is not null
group by datepart(hh, lasteditdate)
union
select datepart(hh, lastactivitydate) 
, 0
, 0
, count(*)
, 0
from posts p 
inner join TagsOnPost ph on ph.postid = p.id
group by datepart(hh, lastactivitydate)
union
select datepart(hh, c.creationdate) 
, 0
, 0
, 0
, count(*)
from comments c 
inner join TagsOnPost ph on ph.postid = c.postid
group by datepart(hh, c.creationdate)
) main
group by hour
order by 1

fork query

edited apr 2 15 grgarside

created mar 29 15 anonymous