Homework over time with time bins

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT
  DATEADD(DD,
   -(DATEDIFF(DD,CreationDate,GETDATE())-DATEDIFF(DD,CreationDate,GETDATE())%##TimeBinDays?7##)
   ,CONVERT(date,GETDATE())) AS RoundedDate,
  COUNT(DISTINCT CASE WHEN DeletionDate IS NOT NULL THEN Id END) AS HomeworkDeleted,
  COUNT(DISTINCT CASE WHEN DeletionDate IS NULL THEN Id END) AS HomeworkNonDeleted,
  COUNT(*) AS HomeworkWithDeleted
FROM PostsWithDeleted
WHERE Tags LIKE '%homework%'
  --AND CreationDate>'2010-01-01'
  --AND CreationDate<'2017-10-10'
GROUP BY DATEADD(DD,
   -(DATEDIFF(DD,CreationDate,GETDATE())-DATEDIFF(DD,CreationDate,GETDATE())%##TimeBinDays?7##)
   ,CONVERT(date,GETDATE()))
   
ORDER BY RoundedDate DESC

Enter Parameters

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