Protection Duration of Posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Quick and clunky examination of most recent protection duration or unprotected posts.

The Great Outdoors

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results