Archive for September, 2008

My Bloody Valentine

Tuesday, September 30th, 2008

so apparently the band My Bloody Valentine is getting back into the studio (finally)
http://www.guardian.co.uk/music/2008/sep/25/my.bloody.valentine.new.album

anyway, i’ve seeded pandora with them and am blissing out as code like a monkey

Tags: ,

How to get only changed values using SQL

Monday, September 29th, 2008

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.

Anniversary of a Dark Day

Thursday, September 11th, 2008

9/11 is now long enough ago, that when i read e-mails from the time I am surprised by how antiquated my technology was.

i didn’t know anybody who died that day, thankfully.

*moment of silence*