DECLARE @questionEndDate AS DATETIME2 = DATEADD( MONTH, -...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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 )

*/

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results