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 [Day of 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
  DATEPART(W, [CreationDate]) [Day of week],
  --CONVERT(DATE, DATEADD(day, (DATEDIFF(day, CreationDate, @LastFullDayOfData) % 7), CreationDate)) [Week],
    PostTypeId
     
  from Posts p
  where [CreationDate] <= @LastFullDayOfData
) as X
group by [Day of week]
order by [Day of week] desc

Enter Parameters

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