Winphone thread volume with 1day reponse by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Signal Processing Meta

Q&A about the site for practitioners of the art and science of signal, image and video processing

SELECT COUNT(*) as Volume, 
table1.[Year] as [Year],
table1.Month as Month
from (
SELECT distinct p1.Id as ID,
DATEPART(year, p1.CreationDate) as [Year], 
DATEPART(month, p1.CreationDate) as Month
FROM Posts p1 
INNER JOIN Posts p2 on p1.Id = p2.ParentId 
and p1.PostTypeId = 1 and p2.PostTypeId = 2 and p1.CommunityOwnedDate is null and p1.ClosedDate is null
and DATEDIFF(day, p1.CreationDate, p2.CreationDate) < 1
WHERE (p1.Tags like '%<windows-phone%' or p1.Tags like '%<wp7%')
UNION
SELECT distinct p.Id as ID,
DATEPART(year, p.CreationDate) as [Year], 
DATEPART(month, p.CreationDate) as Month
FROM Posts p 
INNER JOIN Comments c on p.Id = c.PostId
and p.PostTypeId = 1 and p.CommunityOwnedDate is null and p.ClosedDate is null
and DATEDIFF(day, p.CreationDate, c.CreationDate) < 1
WHERE (p.Tags like '%<windows-phone%' or p.Tags like '%<wp7%')) as table1
GROUP BY table1.[Year], table1.Month
ORDER BY [Year], Month

Enter Parameters

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