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)

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 */
    A.LocationId, A.ConnectionStatus, A.Comments,
        A.TimeRecorded AS StatusChangeTime

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

    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*