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 ( --------TIER 2---------- --9652074, --Saps --9652169, --Nelson --8573097, --Rafon --8566739, --Henri --8579073, --James --------TIER 1---------- 9455567, --Xer 9460075, --Micah 9460321, --Mervin 9461114, --Tom 5993323 --Ricky ) 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 ( --------TIER 2---------- --9652074, --Saps --9652169, --Nelson --8573097, --Rafon --8566739, --Henri --8579073, --James --------TIER 1---------- 9455567, --Xer 9460075, --Micah 9460321, --Mervin 9461114, --Tom 5993323 --Ricky ) 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