With questiontypeweek as ( select id, DATEADD(wk, DATEDIF...

0

Please login or register to vote for this query.

(click on this box to dismiss)

DevOps Meta

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

With questiontypeweek as (
    select 
        id,
        DATEADD(wk, DATEDIFF(wk, 0, creationdate), 0) as week_date,
        (CASE 
            WHEN ClosedDate is not null THEN 'closed' 
            ELSE (CASE 
                    WHEN acceptedanswerid is not null THEN 'accepted'
                    ELSE 'open' 
                 END) 
        END) as type
    from posts p
    where posttypeid = 1
)
select 
    CONVERT(varchar(10), a.week_date, 101) as week,
    (isnull(a.accepted, 0) * 100 / d.total) as Accepted, 
    (isnull(b.closed, 0) * 100 / d.total) as Closed, 
    (isnull(c.[open], 0) * 100 / d.total) as [Open]
from 
    (select count(*) as accepted, week_date 
     from questiontypeweek 
     where type = 'accepted'
     group by week_date) a
left join 
    (select count(*) as closed, week_date 
     from questiontypeweek 
     where type = 'closed'
     group by week_date) b
on 
    a.week_date = b.week_date
left join 
    (select count(*) as [open], week_date 
     from questiontypeweek 
     where type = 'open' 
     group by week_date) c
on 
    a.week_date = c.week_date
left join 
    (select count(*) as total, week_date 
     from questiontypeweek
     group by week_date) d
on 
    a.week_date = d.week_date
order by 
    a.week_date;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results