5 Replies Latest reply on Jul 25, 2013 5:42 PM by RichardLetts

    SQL Question on comparing Custom Poller table values

    mharvey

      Has anyone been able to create a SQL query that compares the current polled values of a custom poller table and historic values then report when any of the values of the rows have changed?  I've been trying to write something to compare rows based on custom poller assignment ID and rowID then report back of the current polled data held in the CustomPollerStatus table is different than data in either the CustomPollerStatuistics_Detail table or even the CustomPollerStatistics View.  Any thoughts would be we

        • Re: SQL Question on comparing Custom Poller table values
          Sateesh Bolla

          Hi mharvey,

           

          I am also looking for same functionality. Any good luck with you? Could you please let me know if you found any work around for that?

           

          Thanks,

          Sateesh

          • Re: SQL Question on comparing Custom Poller table values
            RichardLetts

            well, if your custom poller is defined as a rate then:

            Select Top 1000 * From CustomPollerStatus where Rate <> RawStatus

            (or am I missing something here?)

             

            if you want to compare historical data the something like:

             

            SELECT TOP 1000 *

            FROM   custompollerstatus CPS

                   INNER JOIN custompollerstatistics_detail CPSD

                           ON CPS.custompollerassignmentid = CPSD.custompollerassignmentid

                              AND CPS.rowid = CPSD.rowid

            WHERE  cps.rate = cps.rawstatus

                   AND CPS.rawstatus <> CPSD.rawstatus

                   AND CPSD.datetime = (SELECT Max(datetime)

                                        FROM   custompollerstatistics_detail CPSD2

                                        WHERE

                           CPSD2.custompollerassignmentid = CPSD.custompollerassignmentid

                           AND CPSD2.rowid = CPSD.rowid)



              • Re: SQL Question on comparing Custom Poller table values
                mharvey

                Thanks Richard.  The historical is what I was after.  This is exactly what I needed.  Thank you.

                  • Re: SQL Question on comparing Custom Poller table values
                    RichardLetts

                    You're welcome; In the spirirt of more than one way to do something in SQL, the following query might run faster in some environments:

                    SELECT TOP 1000 *

                    FROM   custompollerstatus CPS

                           INNER JOIN custompollerstatistics_detail CPSD

                                   ON CPS.custompollerassignmentid = CPSD.custompollerassignmentid

                                      AND CPS.rowid = CPSD.rowid

                           INNER JOIN (SELECT Max(datetime) AS DateTime, custompollerassignmentid, rowid

                                       FROM   custompollerstatistics_detail

                                       GROUP  BY custompollerassignmentid, rowid) CPSD2

                                   ON CPS.custompollerassignmentid = CPSD2.custompollerassignmentid

                                      AND CPS.rowid = CPSD2.rowid

                                      AND CPSD.datetime = CPSD2.datetime

                    WHERE  CPS.rawstatus <> CPSD.rawstatus

                    1 of 1 people found this helpful