0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for engineers, producers, editors, and enthusiasts spanning the fields of audio, video, and media creation

Select a.Id, a.CreationDate, a.DisplayName, a.Title, a.Tags, a.Link, b.UpMod as Upvote, b.DownMod as DownVote, b.AcceptedByOriginator as Accepted
From 
(Select Distinct a.Id, a.CreationDate, u.DisplayName, p.Title, p.Tags, 
   CONCAT('http://stackoverflow.com/questions/', p.Id, '#answer-', a.Id) as Link
   --,vu.DisplayName
From Posts a 
   Inner Join Posts p On a.ParentId = p.Id 
   Inner Join Users u On a.OwnerUserId = u.Id
   Inner Join PostTags pt on p.Id = Pt.PostId
   Inner Join Tags t on pt.TagId = t.Id
   --Left Join Votes v on a.Id = v.PostId and v.VoteTypeId = 2 
   --Left Join Users vu on v.UserId = vu.Id
Where a.OwnerUserId in (
      10841880,
      11025839,
      10752132,
      9460075,
      8573097,
      11269204,
      11439407
  )
  And a.PostTypeId = 2  -- Answer
  And p.PostTypeId = 1  -- Question
  And t.TagName in ('google-maps-api-3','google-maps', 'google-maps-api',
  'google-places-api', 'google-static-maps', 'google-street-view',
  'google-geocoding-api', 'google-direction', 'google-distancematrix-api',
  'google-maps-android-api-2', 'google-maps-sdk-ios', 'google-maps-embed',
  'google-maps-mobile', 'google-geolocation', 'google-maps-timezone',
  'google-elevation-api', 'google-roads-api')
  And a.CreationDate >= DATEADD(Day, -30, Getdate())  -- 30 days answers 
  ) a Left Join (
  SELECT *
FROM (
  SELECT v.PostID as Id, vt.Name, COUNT(*) AS CNT
     FROM 
     Posts p
       INNER JOIN Votes v
         ON v.PostId = p.Id
       INNER JOIN VoteTypes vt
         ON v.VoteTypeId = vt.Id
     WHERE
     p.OwnerUserId in
     (
      10841880,
      11025839,
      10752132,
      9460075,
      8573097,
      11269204,
      11439407
      ) 
  GROUP BY 
  vt.Name, v.PostID) s_tab pivot (min(CNT)for [Name] in ([UpMod], [DownMod],[AcceptedByOriginator]))AS PVT
  ) b on a.Id = b.Id
  Order by a.DisplayName, a.CreationDate desc

fork query

edited jun 18 anonymous

created apr 13 18 tomjosef