ELL Year 1 month-to-month cumulative answers by unlinked User

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

declare @ld0 as date = '2013-01-30'; -- Private Beta
declare @ld1 as date = '2013-02-27'; -- Jan-Feb 
declare @ld2 as date = '2013-03-30'; -- Feb-Mar
declare @ld3 as date = '2013-04-29'; -- Mar-Apr
declare @ld4 as date = '2013-05-30'; -- Apr-May
declare @ld5 as date = '2013-06-29'; -- May-Jun
declare @ld6 as date = '2013-07-30'; -- Jun-Jul
declare @ld7 as date = '2013-08-30'; -- Jul-Aug
declare @ld8 as date = '2013-09-29'; -- Aug-Sep
declare @ld9 as date = '2013-10-30'; -- Sep-Oct
declare @ld10 as date = '2013-11-29'; -- Oct-Nov
declare @ld11 as date = '2013-12-30'; -- Nov-Dec
declare @ld12 as date = '2014-01-30'; -- Dec-Jan

select
  u.OwnerDisplayName,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld0) as PrB, 
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld1) as Mo01, 
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld2) as Mo02,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld3) as Mo03,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld4) as Mo04, 
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld5) as Mo05, 
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld6) as Mo06,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld7) as Mo07,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld8) as Mo08,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld9) as Mo09, 
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld10) as Mo10, 
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld11) as Mo11,
  (select count(Id) from Posts a where a.OwnerDisplayName=u.OwnerDisplayName and a.PostTypeId=2 and a.CreationDate <= @ld12) as Mo12
from Posts u
where u.OwnerUserId is null  
group by u.OwnerDisplayName 
order by u.OwnerDisplayName

Enter Parameters

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