Q&A for professional and enthusiast programmers
DECLARE @questionEndDate AS DATETIME2 = DATEADD( MONTH, -18, GETDATE() ) DECLARE @questionStartDate AS DATETIME2 = DATEADD( DAY, -1, @questionEndDate ) --DROP TABLE ##Questions SELECT Questions.Id INTO ##Questions FROM Posts AS Questions WITH ( NOLOCK ) WHERE Questions.PostTypeId = 1 -- questions AND Questions.CreationDate BETWEEN @questionStartDate AND @questionEndDate AND Questions.ClosedDate IS NULL AND Questions.CommunityOwnedDate IS NULL OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SELECT COUNT(*) AS Questions FROM ##Questions DECLARE @months AS INT = 1 DECLARE @answerStartDate AS DATETIME2 = DATEADD( MONTH, @months, @questionStartDate ) DECLARE @answerEndDate AS DATETIME2 = DATEADD( MONTH, 1, @answerStartDate ) SELECT SUM( CASE WHEN Answers.CreationDate BETWEEN DATEADD( MONTH, 1, @questionStartDate ) AND DATEADD( MONTH, 2, @questionStartDate ) THEN 1 ELSE 0 END ) AS '1-2 months', SUM( CASE WHEN Answers.CreationDate BETWEEN DATEADD( DAY, 2, @questionStartDate ) AND DATEADD( DAY, 3, @questionStartDate ) THEN 1 ELSE 0 END ) AS '2-3 months', SUM( CASE WHEN Answers.CreationDate BETWEEN DATEADD( DAY, 90, @questionStartDate ) AND DATEADD( DAY, 120, @questionStartDate ) THEN 1 ELSE 0 END ) AS '90-120 days' FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId --WHERE -- Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate GROUP BY Answers.CreationDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) /* SET @months = 2 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 3 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 4 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 5 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 6 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 7 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 8 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 9 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 10 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 11 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 12 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 13 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 14 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) SET @months = 15 SET @answerStartDate = DATEADD( MONTH, @months, @questionStartDate ) SET @answerEndDate = DATEADD( MONTH, 1, @answerStartDate ) SELECT COUNT(*) FROM ##Questions AS Questions WITH ( NOLOCK ) INNER JOIN Posts AS Answers WITH ( NOLOCK ) ON Questions.Id = Answers.ParentId WHERE Answers.CreationDate BETWEEN @answerStartDate AND @answerEndDate OPTION ( FORCE ORDER, LOOP JOIN, MAXDOP 1 ) */