SELECT DATEDIFF(DAY, qg.QCreationDate, CAST('2017-10-22 0...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Beer, Wine & Spirits Meta Stack Exchange

Q&A for alcoholic beverage aficionados and those interested in beer, wine, or spirits

SELECT 	
    DATEDIFF(DAY, qg.QCreationDate, CAST('2017-10-22 00:00:00.0' AS DATETIME)) AS QAge,
	qg.*,
	CASE WHEN qg.ACreationDate IS NULL THEN NULL ELSE DATEDIFF(MINUTE, qg.QCreationDate, qg.ACreationDate) END AS QTime2Answer,
	CASE WHEN qg.ACreationDate IS NULL THEN 'FALSE' ELSE 'TRUE' END AS QAnswered
FROM
	(SELECT 
		q.Id AS QID,
		q.AnswerCount AS QAnswerCount,
		q.ViewCount AS QViewCount,
		q.CommentCount AS QCommentCount,
		q.FavoriteCount AS QFavoriteCount,
		q.Tags AS QTags,
		q.Score AS QScore,
		q.Title AS QTitle,
		q.CreationDate AS QCreationDate,
		COUNT(a.Id) AS AnswerCount,
		MIN(a.CreationDate) AS ACreationDate,
		COUNT(b.PostId) AS QBountyCount,
		MIN(b.CreationDate) AS QBCreationDate,
		SUM(b.BountyAmount) AS QBAmount
	FROM
		Posts q
		LEFT JOIN
		Posts a
		ON (q.Id = a.ParentId)
		LEFT JOIN
		(SELECT 
			v.PostId,
            v.BountyAmount,
            qh.CreationDate
		FROM 
			Votes v
			JOIN PostHistory qh 
			ON (v.PostId = qh.PostId) 
		WHERE 
			v.VoteTypeId = 8 AND
			qh.PostHistoryTypeId = 33 AND
			qh.CreationDate >= '2017-10-08 00:00:00.0') b 
		ON (q.Id = b.PostId)		
	WHERE
		q.PostTypeId = 1 AND
		q.ClosedDate IS NULL AND
        (a.Id IS NULL OR q.OwnerUserId <> a.OwnerUserId) AND
		q.CreationDate >= '2017-10-01 00:00:00.0' AND
        q.CreationDate < '2017-10-08 00:00:00.0'
	GROUP BY
		q.Id,
		q.AnswerCount,
		q.ViewCount,
		q.CommentCount,
		q.FavoriteCount,
		q.Tags,
		q.Score,
		q.Title,
		q.CreationDate
	) qg
ORDER BY
	qg.QID

Enter Parameters

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