0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professionals in medical and allied health fields, students of those professions, related academics, and others with a sound understanding of medicine and healthcare-related sciences

DECLARE @StartTime DATETIME = convert(DATETIME, '##StartTime?2017/01/01 00:00:00##')
DECLARE @EndTime DATETIME = dateadd(day,-14,getdate())

select
  MonthOf,
  sum(Answered)/cast(count(*) as decimal) as 'RateAnswered',
  sum(Answered14Days)/cast(count(*) as decimal) as 'RateAnsweredIn14Days',
  sum(Answered7Days)/cast(count(*) as decimal) as 'RateAnsweredIn7Days'
from (select
    dateadd(day, 1-datepart(day, CreationDate), convert(date,CreationDate)) as 'MonthOf',
    -- find some answer within 14 days
    cast(case when exists (select * from Posts a where
      a.ParentId = q.Id
      and datediff(second, q.CreationDate, a.CreationDate) < 1209600 -- 14 days
      ) then 1 else 0 end as int) as 'Answered14Days',
    cast(case when exists (select * from Posts a where
      a.ParentId = q.Id
      and datediff(second, q.CreationDate, a.CreationDate) < 604800 -- 7 days
      ) then 1 else 0 end as int) as 'Answered7Days',
    cast(case when exists (select * from Posts a where
      a.ParentId = q.Id
      ) then 1 else 0 end as int) as 'Answered'
  from
    PostsWithDeleted q
  where
    q.CreationDate > @StartTime and q.CreationDate < @EndTime
    and q.PostTypeId = 1 -- questions only
    and q.ClosedDate is null -- not closed
) t
group by
  MonthOf
order by
  MonthOf desc

fork query

edited jan 17 20 anonymous

created jan 15 20 AnonyMousse