Questions and Answers per 7 day period going backward from now

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

DECLARE @LastFullDayOfData DATE;

SELECT @LastFullDayOfData = DATEADD(DAY, -1, MAX(CONVERT(DATE, [CreationDate])))
FROM [Posts]
print @LastFullDayOfData
select top 100 [Week], 
       sum(case when PostTypeId = 1 then 1 else 0 end) Questions,
       sum(case when PostTypeId = 1 then 0 else 1 end) Answers
from
(
  select
  CONVERT(DATE, DATEADD(day, (DATEDIFF(day, CreationDate, @LastFullDayOfData) % 7), CreationDate)) [Week],
    PostTypeId
     
  from Posts p
  where [CreationDate] <= @LastFullDayOfData
) as X
group by [Week]
order by [Week] desc

Enter Parameters

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