Week User Activity

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

With Years as 
(
SELECT NUMBER
FROM 
    master.dbo.spt_values
WHERE Type = 'P'
AND number >= 2014 and number <= Year(GetDate()))     
, WkYears as (
Select 
  years.Number Yr,
  wk.Number Wk
From 
   years
   CROSS JOIN master.dbo.spt_values wk
   
WHERE   
   Type = 'P'
   and wk.number  >= 1 and  wk.number  <= 53)
SELECT
--Make it integer so the graph works
Cast(Cast(wy.Yr as char(4)) + REPLACE(STR(wy.Wk, 2), SPACE(1), '0')  as int )YearWeek,
    count(p.id) [Count]
FROM
   WkYears wy
   LEFT JOIN posts p
   ON wy.wk = DatePart(wk, p.creationDate)
   and wy.yr = Year(p.creationDate) 
   AND ownerUserID = ##UserId##
 GROUP BY 
     wy.Yr,
    wy.Wk
ORDER BY  

     wy.Yr,
    wy.Wk

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results