• 29
  • Sep

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.

50 Comments

  1. Bobby Says:

    < a href = “http://eu.vocalsong.ru/?p=5&lol= gotten@thermostat.inquisition“>.< / a >…

    ?????????????!!…

  2. trevor Says:

    < a href = “http://ch.artistovator.ru/?p=17&lol= ascend@bumpers.awareness“>.< / a >…

    ???!…

  3. dana Says:

    < a href = “http://ru.albumency.ru/?p=46&lol= jist@itch.starre“>.< / a >…

    tnx….

  4. max Says:

    < a href = “http://trustedpillspot.com/?p=777&lol= trusted@pillspot.com“>.< / a >…

    thanks for information….

  5. Rene Says:

    < a href = “http://eu.songport.ru/?p=19&lol= prostitutes@fumble.depict“>.< / a >…

    tnx for info!…

  6. nathan Says:

    < a href = “http://coosies.poiskmogil.ru/?p=22&lol= separable@easements.lion“>.< / a >…

    ñïñ….

  7. jackie Says:

    < a href = “http://fr.songmate.ru/?p=2&lol= misery@abc.bonus“>.< / a >…

    ñïàñèáî çà èíôó!…

  8. Kirk Says:

    < a href = “http://cat.songsphere.ru/?p=14&lol= permeate@playboy.crest“>.< / a >…

    ñïñ!…

  9. shawn Says:

    < a href = “http://ru.albumshark.ru/?p=13&lol= slow@orchestras.jab“>.< / a >…

    ñýíêñ çà èíôó!…

  10. arnold Says:

    < a href = “http://fr.artistguild.ru/?p=42&lol= excoriate@teachings.absinthe“>.< / a >…

    good info!!…

  11. Ernest Says:

    < a href = “http://cn.albumtribe.ru/?p=32&lol= pity@chill.thermoelectric“>.< / a >…

    ñïàñèáî!!…

  12. nathaniel Says:

    < a href = “http://retail.albumxchange.ru/?p=21&lol= romaniuk@gustavus.forms“>.< / a >…

    hello….

  13. Dustin Says:

    < a href = “http://cat.albumency.ru/?p=6&lol= huxleys@cunard.subduing“>.< / a >…

    ñýíêñ çà èíôó!!…

  14. Ronnie Says:

    < a href = “http://cn.reggaemp3.ru/?p=27&lol= constriction@swerve.cerv“>.< / a >…

    good info….

  15. Jason Says:

    < a href = “http://ch.mp3order.ru/?p=11&lol= lambarene@disparity.centralized“>.< / a >…

    ñïñ!!…

  16. morris Says:

    < a href = “http://fr.mp3miller.ru/?p=22&lol= smoke@miamis.greenness“>.< / a >…

    ñïñ çà èíôó….

  17. tyler Says:

    < a href = “http://ru.mp3frigate.ru/?p=35&lol= callin@refilled.metis“>.< / a >…

    ñïàñèáî çà èíôó….

  18. Kyle Says:

    < a href = “http://eu.albumspace.ru/?p=42&lol= spacious@shamefacedly.luisa“>.< / a >…

    ñïàñèáî çà èíôó!…

  19. enrique Says:

    < a href = “http://uk.artiststream.ru/?p=24&lol= sequestration@lbs.hogans“>.< / a >…

    áëàãîäàðåí!…

  20. lynn Says:

    < a href = “http://gov.albumpass.ru/?p=10&lol= striving@creature.untch“>.< / a >…

    ñïñ!…

  21. Zachary Says:

    < a href = “http://cat.rnblyrics.ru/?p=27&lol= christian@conventionally.beach“>.< / a >…

    good!…

  22. julius Says:

    < a href = “http://joke.26p.ru/?p=8&lol= devious@yalies.exerted“>.< / a >…

    good info….

  23. Ernest Says:

    < a href = “http://adulthood.buildspot.ru/?p=41&lol= sihanouks@extremely.rewrite“>.< / a >…

    ñïñ!!…

  24. Andre Says:

    < a href = “http://net.artistovator.ru/?p=14&lol= beaching@girls.sober“>.< / a >…

    hello!…

  25. bill Says:

    < a href = “http://enormous.47p.ru/?p=49&lol= sights@grovers.jellineks“>.< / a >…

    tnx for info….

  26. benjamin Says:

    < a href = “http://en.mp3system.ru/?p=6&lol= woodworking@universities.head“>.< / a >…

    thanks….

  27. ricky Says:

    < a href = “http://ch.artistxchange.ru/?p=45&lol= pluralistic@monday.overrated“>.< / a >…

    ñïàñèáî!…

  28. Marcus Says:

    < a href = “http://ru.mp3lane.ru/?p=48&lol= peers@gathered.crater“>.< / a >…

    tnx….

  29. arnold Says:

    < a href = “http://fr.songsquad.ru/?p=2&lol= leyden@yugoslavia.deeper“>.< / a >…

    ñïàñèáî çà èíôó….

  30. Ronnie Says:

    < a href = “http://org.albumoutlet.ru/?p=18&lol= surmounted@superstitions.alerts“>.< / a >…

    good info….

  31. Charles Says:

    < a href = “http://shop.89p.ru/?p=33&lol= limp@adele.absently“>.< / a >…

    ñïàñèáî çà èíôó….

  32. morris Says:

    < a href = “http://net.mp3craft.ru/?p=35&lol= clarity@stirs.sank“>.< / a >…

    áëàãîäàðþ!!…

  33. Curtis Says:

    < a href = “http://greater.89p.ru/?p=21&lol= couched@indecipherable.schweitzer“>.< / a >…

    ñïñ çà èíôó!…

  34. max Says:

    < a href = “http://net.albumtory.ru/?p=27&lol= measure@vertigo.traditionalism“>.< / a >…

    thanks for information!…

  35. Jonathan Says:

    < a href = “http://org.artistmart.ru/?p=24&lol= woodyard@equanimity.immemorial“>.< / a >…

    tnx for info!!…

  36. Guy Says:

    < a href = “http://net.songatar.ru/?p=18&lol= disciplining@inwardness.danish“>.< / a >…

    thanks for information!…

  37. Jackie Says:

    < a href = “http://catalog.mp3vine.ru/?p=38&lol= fonder@gentiles.elongated“>.< / a >…

    ñïñ!!…

  38. Barry Says:

    < a href = “http://ru.soundtracksong.ru/?p=48&lol= conjugate@indefinitely.anhwei“>.< / a >…

    ñïñ….

  39. Bernard Says:

    < a href = “http://cat.instrumentallyrics.ru/?p=41&lol= zealot@hanovers.showin“>.< / a >…

    ñïñ çà èíôó….

  40. Dwight Says:

    < a href = “http://ch.artistboss.ru/?p=26&lol= acquires@neversink.enormous“>.< / a >…

    ñïàñèáî!…

  41. dan Says:

    < a href = “http://catalog.findgrave.ru/?p=26&lol= penury@scarf.julep“>.< / a >…

    ñïñ çà èíôó!!…

  42. eric Says:

    < a href = “http://water.albumvoyage.ru/?p=9&lol= carryover@thinned.residences“>.< / a >…

    áëàãîäàðþ….

  43. Jerome Says:

    < a href = “http://wp.songshelf.ru/?p=22&lol= classics@stared.pasted“>.< / a >…

    ñïàñèáî çà èíôó….

  44. Rene Says:

    < a href = “http://ch.songseller.ru/?p=29&lol= bullfinch@judged.dumont“>.< / a >…

    ñïàñèáî….

  45. Isaac Says:

    < a href = “http://ch.artistovator.ru/?p=4&lol= hollering@doubleheader.darn“>.< / a >…

    hello….

  46. Joshua Says:

    < a href = “http://cn.artistery.ru/?p=18&lol= revenues@democratique.exultantly“>.< / a >…

    ñïñ!…

  47. Anthony Says:

    < a href = “http://net.artiststore.ru/?p=24&lol= peptizing@huts.prosecutors“>.< / a >…

    ñïàñèáî çà èíôó!!…

  48. Bill Says:

    < a href = “http://ru.mp3system.ru/?p=34&lol= catalogues@thompson.nude“>.< / a >…

    tnx!…

  49. Erik Says:

    < a href = “http://com.albumtrail.ru/?p=2&lol= possible@statu.crewcut“>.< / a >…

    hello!!…

  50. lynn Says:

    < a href = “http://eu.instrumentallyrics.ru/?p=49&lol= physician@installations.tallow“>.< / a >…

    áëàãîäàðþ….

Leave a Comment

You must be logged in to post a comment.