0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for movie and tv enthusiasts

DECLARE @BeginDate DATETIME = (SELECT min(CreationDate) FROM PostHistory);
DECLARE @EndDate DATETIME = (SELECT max(CreationDate) FROM PostHistory);
DECLARE @Pattern VARCHAR(25) = ##TagPattern:string?%identify-this-%##;

WITH Dates AS
(
  SELECT @BeginDate AS [DateValue]
  UNION ALL
  SELECT dateadd(month, 1, DateValue) FROM Dates WHERE dateadd(month, 1, DateValue) < @EndDate
)

SELECT D.DateValue AS [Date],

  (SELECT count(*) FROM Posts P WHERE P.PostTypeId = 1 AND 
    datepart(year, P.CreationDate) = datepart(year, D.DateValue) AND 
    datepart(month, P.CreationDate) = datepart(month, D.DateValue)) AS [Total],
  (SELECT count(*) FROM Posts P WHERE P.Tags LIKE @Pattern AND 
    datepart(year, P.CreationDate) = datepart(year, D.DateValue) AND 
    datepart(month, P.CreationDate) = datepart(month, D.DateValue)) AS [ID],
  (SELECT count(*) FROM Posts P WHERE P.Tags NOT LIKE @Pattern AND 
    datepart(year, P.CreationDate) = datepart(year, D.DateValue) AND 
    datepart(month, P.CreationDate) = datepart(month, D.DateValue)) AS [Non-ID]
/*
  (CASE WHEN EXISTS (SELECT * FROM Posts P WHERE P.PostTypeId = 1 AND 
    datepart(year, P.CreationDate) = datepart(year, D.DateValue) AND 
    datepart(month, P.CreationDate) = datepart(month, D.DateValue))
   THEN
    100.0 * (SELECT count(*) FROM Posts P WHERE P.Tags LIKE @Pattern AND 
      datepart(year, P.CreationDate) = datepart(year, D.DateValue) AND 
      datepart(month, P.CreationDate) = datepart(month, D.DateValue)) / 
    (SELECT count(*) FROM Posts P WHERE P.PostTypeId = 1 AND 
      datepart(year, P.CreationDate) = datepart(year, D.DateValue) AND 
      datepart(month, P.CreationDate) = datepart(month, D.DateValue)) 
   ELSE 0 END) AS [ID %]*/
FROM Dates AS D
ORDER BY D.DateValue ASC
OPTION (MAXRECURSION 0)

fork query

edited feb 3 18

created aug 16 15 Rauy