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.

Q&A about the site for users and developers of hardware and software for Raspberry Pi

--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)
  AS (
    select * 
    from (
      select TOP(20) PostId, CreationDate,
            ROW_NUMBER() over (partition by PostId order by CreationDate desc) as Rank
      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],
       DATEDIFF(day, ProtectDate, UnprotectDate) as 'Protect Duration (days)'
FROM RecentUnprotects AS un
INNER JOIN RecentProtects AS pro ON un.PostId = pro.PostId

view current version

edited jul 9 20

created jul 9 20 Garm