1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

--I wanted to see most recent activity by a mod
--of course, I can only restrict this to mod actions that are publicly visible 
--However, either I made a mistake, or larger PostHistory.Id is not the same as more recent action

--actions that can only be done by moderators

--14 = Post Locked - post locked by moderator
--15 = Post Unlocked - post unlocked by moderator
--16 = Community Owned - post now community owned
--QUESTION can only be made CW by a moderator (so I look only at question; PostTypeId=1)
--37 = Post merge source
--38 = Post merge destination

;with recent_cte AS
(
SELECT
  ph.UserId,
  MAX(ph.Id) AS MaxId
  FROM PostHistory ph INNER JOIN Posts p
  ON ph.PostId=p.Id
  AND ph.PostHistoryTypeId IN (14,15,16,37,38)
  AND p.PostTypeId=1
GROUP BY ph.UserId
)

SELECT ph.CreationDate, ph.UserId AS [User Link], MaxId,  ph.PostId AS [Post Link],   concat('site://posts/',
          ph.PostId,
          '/revisions'
          ) [Revisions Link]
FROM recent_cte inner JOIN PostHistory ph ON MaxId = ph.Id
ORDER BY ph.CreationDate DESC

fork query

edited nov 30 18

created nov 30 18 Martin20528