Quick and clunky examination of most recent protection duration or unprotected posts.
Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment
--SELECT PostId, CreationDate as 'EventDate', -- ROW_NUMBER() over( PARTITION by PostId order by CreationDate desc) as 'URank' --FROM PostHistory --WHERE PostHistoryTypeId = 19 AND URank = 1 -- Most Recent Protections ; WITH RecentProtects (PostId, ProtectDate, Rank) AS ( select * from ( select PostId, CreationDate, ROW_NUMBER() over (partition by PostId order by CreationDate desc) as Rank from PostHistory where PostHistoryTypeId = 19)t where Rank = 1 ), -- Most Recent Unprotects RecentUnprotects (PostId, UnprotectDate, Rank, CreationDate) AS ( select * from ( select PostId, CreationDate, ROW_NUMBER() over (partition by PostId order by CreationDate desc) as Rank, CreationDate as 'EventDate' from PostHistory where PostHistoryTypeId = 20)t where Rank = 1 ) -- Show me the Duration of protection for the most recent unprotects SELECT un.PostId As [Post Link], ProtectDate, UnprotectDate, DATEDIFF(day, ProtectDate, UnprotectDate) as 'Protect Duration (days)',DATEDIFF(day, un.CreationDate, CURRENT_TIMESTAMP) as 'Post Age (days)' FROM RecentUnprotects AS un INNER JOIN RecentProtects AS pro ON un.PostId = pro.PostId