Lastest data dates in the Data Explorer, approach 3

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 on the first run per site. Previous versions of this query run on all sites but SO (2 min timeout). They also ran on SO before the last month's data was updated. It looks like some datetime cols may not be indexed, investigate further (later). Here we omit several columns, attempting to beat the 2-min timeout.

Computational Science

Q&A for scientists using computers to solve scientific problems

-- Lastest data dates in the Data Explorer, approach 3
-- The SEDE "most recent" displays lie!!
-- WARNING: This query can take a minute on the first run per site.
-- Previous versions of this query run on all sites but SO (2 min timeout).  They also ran on SO before the last month's data was updated.
-- It looks like some datetime cols may not be indexed, investigate further (later).
-- Here we omit several columns, attempting to beat the 2-min timeout.

DECLARE @LatestDates TABLE (
    sortOverride    INT         DEFAULT 0,
    [Table]         CHAR (40),
    Value           CHAR (40),
    [Last Date]     DATETIME
);

WITH Latest 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
)
INSERT INTO @LatestDates ([Table], Value, [Last Date])
SELECT        T AS [Table]
            , Value
            , [Last Date]
FROM        Latest
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);

WITH Latest AS (
    SELECT  'Users' AS T,
            CAST ('CreationDate'       AS CHAR(40) )  C1, MAX (CreationDate)       M1,
            CAST ('LastAccessDate'     AS CHAR(40) )  C2, MAX (LastAccessDate)     M2
    FROM    Users
)
INSERT INTO @LatestDates ([Table], Value, [Last Date])
SELECT        T AS [Table]
            , Value
            , [Last Date]
FROM        Latest
UNPIVOT     (
                Value FOR colName IN
                (C1, C2)
            )AS unP1
UNPIVOT     (
                [Last Date] FOR colName2 IN
                (M1, M2)
            )AS unP2
WHERE       RIGHT (colName, 1) = RIGHT (colName2, 1);

WITH Latest AS (
    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
    FROM    SuggestedEdits
)
INSERT INTO @LatestDates ([Table], Value, [Last Date])
SELECT        T AS [Table]
            , Value
            , [Last Date]
FROM        Latest
UNPIVOT     (
                Value FOR colName IN
                (C1, C2, C3)
            )AS unP1
UNPIVOT     (
                [Last Date] FOR colName2 IN
                (M1, M2, M3)
            )AS unP2
WHERE       RIGHT (colName, 1) = RIGHT (colName2, 1);

/*--- Tables with a single date column don't need unpivot.
*/
INSERT INTO     @LatestDates ([Table], Value, [Last Date])
SELECT TOP 1    'Comments',
                'CreationDate',
                CreationDate
FROM            Comments
ORDER BY        CreationDate DESC
/*
INSERT INTO     @LatestDates ([Table], Value, [Last Date])
SELECT TOP 1    'Badges',
                'Date',
                Date
FROM            Badges
ORDER BY        Date DESC

INSERT INTO     @LatestDates ([Table], Value, [Last Date])
SELECT TOP 1    'PostFeedback',
                'CreationDate',
                CreationDate
FROM            PostFeedback
ORDER BY        CreationDate DESC

INSERT INTO     @LatestDates ([Table], Value, [Last Date])
SELECT TOP 1    'PostHistory',
                'CreationDate',
                CreationDate
FROM            PostHistory
ORDER BY        CreationDate DESC
*/
 
INSERT INTO     @LatestDates ([Table], Value, [Last Date])
SELECT TOP 1    'SuggestedEditVotes',
                'CreationDate',
                CreationDate
FROM            SuggestedEditVotes
ORDER BY        CreationDate DESC

INSERT INTO     @LatestDates ([Table], Value, [Last Date])
SELECT TOP 1    'Votes',
                'CreationDate',
                CreationDate
FROM            Votes
ORDER BY        CreationDate DESC

INSERT INTO @LatestDates (sortOverride, [Table], Value, [Last Date])
SELECT      1, '*MAX*', '*OVERALL*', MAX ([Last Date]) FROM @LatestDates

SELECT      [Table], Value, [Last Date]
FROM        @LatestDates
ORDER BY    sortOverride DESC,
            [Table],
            Value

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results