create table #tmp_dates (date datetime) insert into #tmp_...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

create table #tmp_dates (date datetime)
insert into #tmp_dates values ('20090801')
insert into #tmp_dates values ('20090901')
insert into #tmp_dates values ('20091001')
insert into #tmp_dates values ('20091101')
insert into #tmp_dates values ('20091201')
insert into #tmp_dates values ('20100101')
insert into #tmp_dates values ('20100201')
insert into #tmp_dates values ('20100301')
insert into #tmp_dates values ('20100401')
insert into #tmp_dates values ('20100501')
insert into #tmp_dates values ('20100601')
insert into #tmp_dates values ('20100701')
insert into #tmp_dates values ('20100801')
insert into #tmp_dates values ('20100901')
insert into #tmp_dates values ('20101001')
insert into #tmp_dates values ('20101101')
insert into #tmp_dates values ('20101201')
insert into #tmp_dates values ('20110101')
insert into #tmp_dates values ('20110201')
insert into #tmp_dates values ('20110301')
insert into #tmp_dates values ('20110401')
insert into #tmp_dates values ('20110501')

SELECT date, 
       (SELECT Count(*) FROM Posts
           WHERE PostTypeId = 1 
           AND ClosedDate IS NULL
           AND CreationDate < date
       ) AS total_questions,
       (SELECT COUNT(*) FROM Posts AS q
            WHERE PostTypeId=1
            AND ClosedDate IS NULL
            AND CreationDate < date
            AND (AcceptedAnswerId IS NOT NULL
                 OR
                 (SELECT COUNT(*) FROM Posts AS a WHERE PostTypeId=2 AND ParentId=q.id AND Score > 0) > 1)
       ) AS answered_questions
FROM #tmp_dates

Enter Parameters

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