Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of 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