0

Please login or register to vote for this query.

(click on this box to dismiss)

The SEDE "most recent" displays lie!! WARNING: This query can take a minute or two on the first run per site. Note: if date column(s) are missing, it's because there are no such entries (suggested edits, for example) for that site, yet.

Q&A for scientists using computers to solve scientific problems

-- Lastest data dates in the Data Explorer, approach 2
-- The SEDE "most recent" displays lie!!
-- WARNING: This query can take a minute or two on the first run per site.
-- Note: if date column(s) are missing, it's because there are no such entries (suggested edits, for example) for that site, yet.

WITH LatestP AS (
    SELECT  'Posts' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('LastEditDate'       AS CHAR(40) )  C2, MAX (LastEditDate)       M2,
            CAST ('LastActivityDate'   AS CHAR(40) )  C3, MAX (LastActivityDate)   M3,
            CAST ('ClosedDate'         AS CHAR(40) )  C4, MAX (ClosedDate)         M4,
            CAST ('CommunityOwnedDate' AS CHAR(40) )  C5, MAX (CommunityOwnedDate) M5
    FROM    Posts

    UNION ALL
    SELECT  'SuggestedEdits' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('ApprovalDate'       AS CHAR(40) )  C2, MAX (ApprovalDate)       M2,
            CAST ('RejectionDate'      AS CHAR(40) )  C3, MAX (RejectionDate)      M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    SuggestedEdits

    UNION ALL
    SELECT  'Users' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('LastAccessDate'     AS CHAR(40) )  C2, MAX (LastAccessDate)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    Users

    UNION ALL
    SELECT  'TagSynonyms' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('ApprovalDate'       AS CHAR(40) )  C2, MAX (ApprovalDate)       M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    TagSynonyms

    UNION ALL
    SELECT  'Comments' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('NA'                 AS CHAR(40) )  C2, CAST (0 AS DATETIME)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    Comments

    UNION ALL
    SELECT  'Badges' AS T,
            CAST ('Date'               AS CHAR(40) )  C1, MAX (Date)               M1,
            CAST ('NA'                 AS CHAR(40) )  C2, CAST (0 AS DATETIME)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    Badges

    UNION ALL
    SELECT  'PostFeedback' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('NA'                 AS CHAR(40) )  C2, CAST (0 AS DATETIME)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    PostFeedback

    UNION ALL
    SELECT  'PostHistory' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('NA'                 AS CHAR(40) )  C2, CAST (0 AS DATETIME)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    PostHistory

    UNION ALL
    SELECT  'SuggestedEditVotes' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('NA'                 AS CHAR(40) )  C2, CAST (0 AS DATETIME)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    SuggestedEditVotes

    UNION ALL
    SELECT  'Votes' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('NA'                 AS CHAR(40) )  C2, CAST (0 AS DATETIME)     M2,
            CAST ('NA'                 AS CHAR(40) )  C3, CAST (0 AS DATETIME)     M3,
            CAST ('NA'                 AS CHAR(40) )  C4, CAST (0 AS DATETIME)     M4,
            CAST ('NA'                 AS CHAR(40) )  C5, CAST (0 AS DATETIME)     M5
    FROM    Votes
)

SELECT      '*MAX*'                             AS [Table],
            '*OVERALL*'                         AS Value,
            MAX (maxDate)     AS [Last Date]
FROM        LatestP
UNPIVOT     (
                maxDate FOR colNamem IN
                (M1, M2, M3, M4, M5)
            )AS unPm

UNION ALL
SELECT        T AS [Table]
            , Value
            , [Last Date]
FROM        LatestP 
UNPIVOT     (
                Value FOR colName IN
                (C1, C2, C3, C4, C5)
            )AS unP1
UNPIVOT     (
                [Last Date] FOR colName2 IN
                (M1, M2, M3, M4, M5)
            )AS unP2
WHERE       RIGHT (colName, 1) = RIGHT (colName2, 1)
AND         Value <> 'NA'
ORDER BY    [Table],
            Value

fork query

created dec 15 11 Awesome Poodles