0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @userid as int = ##UserID:int##
declare @epoch as datetime
;

select @epoch = DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreationDate), CreationDate)
from users
where Id = @userid
;

with Activity as (

    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 = pt.Id
      and p.PostTypeId in (1, 2)
      and p.CreationDate > @epoch
      and p.OwnerUserId = @userid
    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
    from Comments c
    where c.CreationDate > @epoch
      and UserId = @userid
    Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate))
  
  UNION
    
    SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) as YearMon,
        'History' as Series,
        count(distinct RevisionGUID) as Action
    from PostHistory c
    where c.CreationDate > @epoch
      and UserId = @userid
    Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate))
), ByWeek as (

select *
from Activity
pivot (
    sum(Action)
    for Series in ([Question],[Answer],[Comment],[History])) as Pivoted
where YearMon < CURRENT_TIMESTAMP -- ignore most recent week.

)

Select YearMon,
IsNull(Question, 0) as Question,
IsNUll(Answer, 0) as Answer,
IsNull(Comment, 0) as Comment,
IsNull(History, 0) as History,
IsNull(Question, 0) + IsNUll(Answer, 0) + IsNull(Comment, 0) + IsNull(History, 0) as [All]
from ByWeek main
order by main.YearMon

fork query

edited jul 22 15

created jul 22 15 rolfl