0

Please login or register to vote for this query.

(click on this box to dismiss)

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

declare @weeks int = 300

select
  a as "Weeks Active Since Question",
  -- b as "Unresolved Questions",
  -- c as "Answered Questions",
  100.0 * b / c as "Percent Unresolved"
from (

  select n,

  (select count(*)from Posts P
    left join Users U on P.OwnerUserId = U.Id  
    where P.AnswerCount > 0
    and datediff(week, P.CreationDate, U.LastAccessDate) >= n
    and datediff(week, P.CreationDate, getutcdate()) > @weeks
    and P.AcceptedAnswerId is null
  ),

  (select count(*) from Posts P
    left join Users U on P.OwnerUserId = U.Id 
    where P.AnswerCount > 0
    and datediff(week, P.CreationDate, U.LastAccessDate) >= n
    and datediff(week, P.CreationDate, getutcdate()) > @weeks
  )

  from (
  select top (@weeks) ROW_NUMBER()
    over(order by name)
    from sys.all_objects) m(n)

) t(a,b,c)

fork query

edited aug 17 19

created aug 17 19 anonymous