8 Replies Latest reply on May 14, 2015 6:00 PM by joe.tran

    Custom Report using Custom Pollers

    theroadguy

      We have a report that we run to tell us when we replaced the an UPS batteries last, however I would like to modifiy this query to have it display only UPS batteries that were replaced 3 years ago today (today would be the day that we run the report, so today would have to be dynamic and not a static date).

       

      here is the current query that we are using and it works great.. I just can't seem to make it dance to display only UPS batteries that were replaced over 3 years ago.

       

      Thanks in advance

       

      SELECT

        Nodes.NodeID AS NodeID

      , Caption AS NodeName

      , Nodes.Modal AS Modal

      , CustomPollerAssignment.AssignmentName AS Assignment

      , CustomPollers.UniqueName AS PollerName

      , CAST(CustomPollerStatus.Status AS Date) AS ReplacementDate

      , ISDATE(CustomPollerStatus.Status) AS ISDATEValid

      FROM CustomPollerStatus

       

       

      JOIN CustomPollerAssignment ON CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID

      JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID

      JOIN Nodes ON (Nodes.NodeId = CustomPollerAssignment.NodeId)

       

       

      WHERE (CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate') AND (ISDATE(CustomPollerStatus.Status)  = 1)

       

       

      ORDER BY 6 DESC

        • Re: Custom Report using Custom Pollers
          silverbacksays

          Try adding this in to the WHERE clause:


          CAST > DATEADD(year,-3,GETDATE())


          > DATEADD(year,-3,GETDATE()) is the specific code for looking for something three years ago, you may need to mess about with it a bit, but it should work for you.

          • Re: Custom Report using Custom Pollers
            joe.tran

            Try this ...

             

            SELECT Nodes.NodeID AS NodeID
            , Caption AS NodeName
            , Nodes.Modal AS Modal
            , CustomPollerAssignment.AssignmentName AS Assignment
            , CustomPollers.UniqueName AS PollerName
            , t.ReplacementDate
            , ISDATE(CustomPollerStatus.Status) AS ISDATEValid
            FROM CustomPollerStatus
            
            
            JOIN CustomPollerAssignment ON CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
            JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
            JOIN Nodes ON (Nodes.NodeId = CustomPollerAssignment.NodeId)
            INNER JOIN (SELECT CAST(a.Status AS Date) AS ReplacementDate
                             , a.CustomPollerAssignmentID
                        FROM CustomPollerStatus a
                        WHERE ISDATE(a.Status)  = 1) AS t
                 ON CustomPollerStatus.CustomPollerAssignmentID =  t.CustomPollerAssignmentID 
            WHERE (CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate') 
              AND t.ReplacementDate > DATEADD(year,-3,GETDATE())
            ORDER BY 6 DESC
            

             

            Don't have any Custom Pollers that return datetime values, but the above at least didn't error out on me.