Daily acceptable question count

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

DECLARE @LastFullDayOfData DATE;

SELECT @LastFullDayOfData = DATEADD(DAY, -1, MAX(CONVERT(DATE, [CreationDate])))
FROM [Posts]
print @LastFullDayOfData

SELECT
  TOP ##DayCount##
  CONVERT(DATE, [Date]) [Date],
  COUNT(1) [Acceptable questions]
FROM
(
  SELECT
    [p].[CreationDate] [Date],
    CASE WHEN ([ClosedDate] IS NULL AND [DeletionDate] IS NULL AND [Score] > 0) THEN 1 ELSE 0 END [Acceptable]
  FROM
    [Posts] [p]
  WHERE 1 = 1
    AND [p].[CreationDate] <= @LastFullDayOfData
    AND [p].[PostTypeId] = 1
) [a]
GROUP BY
  CONVERT(DATE, [Date])
ORDER BY
  [Date] DESC

Enter Parameters

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