Duplicates posted over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

-- Get total Questions

select 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) AS [MONTH], 'Total',
  count(*) as Total
from Posts p
where p.posttypeid = 1
group by DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)
--order by [MONTH] desc;



union

-- Get questions closed as duplicates

select 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) AS [MONTH], 'Duplicates',
  count(*) as Duplicates
from Posts p, PostHistory ph
where closeddate is not null 
  and p.id=ph.postid 
  and ph.posthistorytypeid=10
  AND ph.Comment IN ('1', '101')
group by DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)
--order by [MONTH] desc

union

-- Get closed but not duplicate questions

select 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) AS [MONTH], 'Closed not as duplicate',
  count(*) as Duplicates
from Posts p, PostHistory ph
where closeddate is not null 
  and p.id=ph.postid 
  and ph.posthistorytypeid=10
  AND ph.Comment not IN ('1', '101')
group by DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)
--order by [MONTH] desc

union

-- Get closed but not duplicate questions

select 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0) AS [MONTH], 'All Closed',
  count(*) as Duplicates
from Posts p, PostHistory ph
where closeddate is not null 
  and p.id=ph.postid 
  and ph.posthistorytypeid=10
group by DATEADD(MONTH, DATEDIFF(MONTH, 0, p.CreationDate), 0)
order by [MONTH] desc

Enter Parameters

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