So i have a log table that records server status, and what I needed was a query that returns to me only those rows that had status change. That is to say, over time, just show me the times when the status changed.

this query gets me just exactly that — only the rows that represent a change in status from the most recent previous status.

/* main select clause */
SELECT
    A.LocationId, A.ConnectionStatus, A.Comments,
        A.TimeRecorded AS StatusChangeTime

FROM
    /* self-join the table to have a second date field */
    tblNetworkConnectionStatusLog AS A JOIN
    tblNetworkConnectionStatusLog AS B
        ON A.LocationId = B.LocationId
WHERE
    /* only interested in rows of different status */
    B.ConnectionStatus <> A.ConnectionStatus
        /* limited to just the most recent different status */
        AND B.TimeRecorded =
        (
            SELECT
                MAX(C.TimeRecorded) AS StatusChangeTime
            FROM
                dbo.tblNetworkConnectionStatusLog  AS C
            WHERE
                C.LocationId = A.LocationId AND
                C.TimeRecorded < A.TimeRecorded
        )
   
/* this just gets the first status recorded*/
UNION SELECT
    D.LocationId, D.ConnectionStatus, D.Comments,
        D.TimeRecorded AS StatusChangeTime
FROM
    tblNetworkConnectionStatusLog AS D LEFT OUTER JOIN
        tblNetworkConnectionStatusLog AS E
            ON E.LocationId = D.LocationID
                AND E.TimeRecorded < D.TimeRecorded
WHERE
    E.LocationID IS NULL

ORDER BY
    StatusChangeTime DESC

purdy sweet.

» You can leave a comment, or trackback from your own site.