How many [ASP.Net] questions initially tagged [ASP] or [ASP-Classic]?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Initial stab at a query to workout how many ASP.Net questions each month are being incorrectly tagged as ASP or ASP-Classic. It's probably not perfect but it's a start. Added parameter so you can specify how many years to return. 0 - Returns all years 2 - Default value. Designed to accompany the meta post - http://meta.stackoverflow.com/a/272481/692942

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @yearcount AS INT
-- yearcount: The number of years to return: "Use 0 to return all data, default is 2 years." 
SET @yearcount = ##yearcount:int?2##
SELECT CAST('01 ' + DATENAME(MONTH, a.[first_date]) + ' ' + CAST(a.[Year] AS VARCHAR(5)) AS DATETIME) [Month], a.[Total]
FROM (
  SELECT COUNT(*) [total]
  , YEAR(ph.CreationDate) [Year], MONTH(ph.CreationDate) [Month]
  , MIN(ph.CreationDate) [first_date]
  FROM PostHistory ph
  INNER JOIN Posts p ON ph.PostId = p.id
  WHERE PostHistoryTypeId = 3
  AND (
    ([Text] LIKE '%<asp-classic>%' OR [Text] LIKE '%<asp>%') 
    AND ([Text] LIKE '%<%.net%>%' OR [Text] LIKE '%<c#%>%')
  )
  AND p.Tags NOT LIKE '%<asp-classic>%'
  AND (DATEDIFF(YEAR, ph.CreationDate, CAST('01 ' + DATENAME(MONTH, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(5)) AS DATETIME)) <= @yearcount OR @yearcount = 0)
  GROUP BY YEAR(ph.CreationDate), MONTH(ph.CreationDate)
) a
ORDER BY a.[Year] DESC, a.[Month] DESC

/*
SELECT *
FROM PostHistory ph
INNER JOIN Posts p ON ph.PostId = p.id
WHERE PostHistoryTypeId = 3
AND (
  (ph.[Text] LIKE '%<asp-classic>%' OR ph.[Text] LIKE '%<asp>%') 
  AND ph.[Text] LIKE '%<asp.net>%'
)
AND p.Tags NOT LIKE '%<asp-classic>%'
AND ph.CreationDate BETWEEN '01-Aug-2014' AND '31-Aug-2014 23:59:59'
*/

Enter Parameters

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