Refactored The SEDE "most recent" displays lie!!

0

Please login or register to vote for this query.

(click on this box to dismiss)

WARNING: This query can take a minute or two on the first run per site.

Computational Science

Q&A for scientists using computers to solve scientific problems

-- Refactored The SEDE "most recent" displays lie!!
-- WARNING: This query can take a minute or two on the first run per site.

  WITH LatestDates AS (
    SELECT 
          'Posts' as T,
          C, 
          M
    FROM
    (SELECT 
        Max(CreationDate) CreationDate,
        MAX (LastEditDate) LastEditDate,
        MAX (LastActivityDate) LastActivityDate,   
        MAX (ClosedDate) ClosedDate,
        MAX (CommunityOwnedDate) CommunityOwnedDate
    FROM 
        Posts) p
    UNPIVOT
    ( M FOR C in (CreationDate, LastEditDate, LastActivityDate, ClosedDate, CommunityOwnedDate)) unpvt
    UNION SELECT 
          'Users' as T,
          C, 
          M
    FROM
    (SELECT 
        Max(CreationDate) CreationDate,
        MAX (LastAccessDate) LastAccessDate
    FROM 
        Users) p
    UNPIVOT
    ( M FOR C in (CreationDate, LastAccessDate)) unpvt
   
     UNION SELECT 
          'SuggestedEdits' as T,
          C, 
          M
    FROM
    (SELECT 
        Max(CreationDate) CreationDate,
        MAX (ApprovalDate) ApprovalDate,
        MAX (RejectionDate) RejectionDate
    FROM 
        SuggestedEdits) p
    UNPIVOT
    ( M FOR C in (CreationDate, ApprovalDate, RejectionDate)) unpvt
    UNION SELECT 
          'TagSynonyms' as T,
          C, 
          M
    FROM
    (SELECT 
        Max(CreationDate) CreationDate,
        MAX (ApprovalDate) ApprovalDate
    FROM 
        TagSynonyms) p
    UNPIVOT
    ( M FOR C in (CreationDate, ApprovalDate)) unpvt

    UNION SELECT 'SuggestedEditVotes' T, 'CreationDate' C,       CreationDate       M 
    FROM SuggestedEditVotes
    WHERE ID = (SELECT Max(ID) FROM SuggestedEditVotes)

    UNION SELECT 'Comments' T, 'CreationDate' C,       CreationDate       M 
    FROM SuggestedEditVotes
    WHERE ID = (SELECT Max(ID) FROM Comments)

    UNION SELECT 'Badges' T, 'Date' C,       Date M 
    FROM Badges
    WHERE ID = (SELECT Max(ID) FROM Badges)

    UNION SELECT 'PostFeedback' T, 'CreationDate' C,       CreationDate M 
    FROM PostFeedback
    WHERE ID = (SELECT Max(ID) FROM PostFeedback)

    UNION SELECT 'PostHistory' T, 'CreationDate' C,       CreationDate M 
    FROM PostHistory
    WHERE ID = (SELECT Max(ID) FROM PostHistory)


    UNION SELECT 'Votes' T, 'CreationDate' C,       CreationDate M 
    FROM Votes
    WHERE ID = (SELECT Max(ID) FROM Votes)




 
   
)
SELECT              '*MAX*'     AS [Table],
                    '*OVERALL*' AS Value,
                    MAX (M)     AS [Last Date]
FROM                LatestDates
UNION ALL SELECT    T           AS [Table],
                    C           AS Value,
                    M           AS [Last Date]
FROM                LatestDates
ORDER BY            [Table],
                    Value
      

Enter Parameters

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