2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

SELECT 
  DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) AS [Week]
  , COUNT(*) AS QCount
FROM Posts
INNER JOIN PostTags ON PostTags.PostId = Posts.Id
INNER JOIN Tags ON PostTags.TagId = Tags.Id
WHERE Tags.TagName = 'firebase' AND PostTypeId = 1 AND CreationDate > '2015-01-01'
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)
UNION
SELECT 
  DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) AS [Week]
  , COUNT(*) AS GACount
FROM Posts
WHERE CreationDate > '2015-01-01'
  AND OwnerUserId in (
  209103, -- puf
  394010, -- katowulf
  110915, -- mikelehen
  879295, -- rdimarco
  1373162, -- deast
  5385206, -- sganem
  3875023, -- mpmcdonald
  5081766, -- bojeil
  807126, -- dougstevenson
  1410370, -- arthurthompson
  839465, -- mimming
  324977, -- samstern
  819314, -- ianbarber
  1172232, -- gregsoltis
  937583, -- raynor
  613115, -- dgiorgino
  0
)
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)


/*
SELECT 
  DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) AS [Week]
  , COUNT(*) AS QCount
FROM Posts
INNER JOIN PostTags ON PostTags.PostId = Posts.Id
INNER JOIN Tags ON PostTags.TagId = Tags.Id
WHERE Tags.TagName = 'firebase' AND PostTypeId = 1 AND CreationDate > '2015-01-01'
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)
UNION
SELECT 
  DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) AS [Week]
  , COUNT(*) AS GACount
FROM Posts
WHERE CreationDate > '2015-01-01'
  AND OwnerUserId in (
  209103, -- puf
  394010, -- katowulf
  110915, -- mikelehen
  879295, -- rdimarco
  1373162, -- deast
  5385206, -- sganem
  3875023, -- mpmcdonald
  5081766, -- bojeil
  807126, -- dougstevenson
  1410370, -- arthurthompson
  839465, -- mimming
  324977, -- samstern
  819314, -- ianbarber
  1172232, -- gregsoltis
  937583, -- raynor
  613115, -- dgiorgino
  0
)
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)
*/

/*
SELECT
  DATEADD(WEEK, DATEDIFF(WEEK, 0, Q.CreationDate), 0) AS [Week]
  , Q.Id
  , COUNT(*) 
FROM Posts AS Q
INNER JOIN PostTags ON PostTags.PostId = Q.Id
INNER JOIN Tags ON PostTags.TagId = Tags.Id
  AND Tags.TagName = 'firebase' AND Q.PostTypeId = 1 
  AND CreationDate > '2015-01-01'
LEFT JOIN Posts AS A ON A.ParentId = Q.Id
  AND A.OwnerUserId in (
  209103, -- puf
  394010, -- katowulf
  110915, -- mikelehen
  879295, -- rdimarco
  1373162, -- deast
  5385206, -- sganem
  3875023, -- mpmcdonald
  5081766, -- bojeil
  807126, -- dougstevenson
  1410370, -- arthurthompson
  839465, -- mimming
  324977, -- samstern
  819314, -- ianbarber
  1172232, -- gregsoltis
  937583, -- raynor
  613115, -- dgiorgino
  0
)
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, Q.CreationDate), 0), Q.Id
*/

view current version

edited jul 13 16

created jul 12 16 Frank van Puffelen