Q&A for professional and amateur woodworkers
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) AS [Post Month], SUM( CASE WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) <= 2 AND VoteTypeId = 2 THEN 1.0 WHEN DATEDIFF(day, p.CreationDate, v.CreationDate) <= 2 AND VoteTypeId = 3 THEN -1.0 ELSE 0.0 END ) / COUNT(DISTINCT PostId) AS [48Hr Votes] FROM PostsWithDeleted p INNER JOIN Votes v ON v.PostId = p.Id AND VoteTypeId in (2,3) WHERE p.PostTypeId = 1 AND p.CreationDate <= DATEADD(month, -1, GETDATE()) --Don't include current month since it has incomplete data GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)