3 Replies Latest reply on Apr 21, 2009 4:19 PM by lchance

    Filtering in SQL

    elsaonita

      I Have this SQL script

      SELECT
          StartTime.EventTime,
         
          Nodes.Caption,
          DATEDIFF(Mi, StartTime.EventTime,
          (SELECT TOP 1
              EventTime
              FROM Events AS Endtime
              WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
                  AND EndTime.NetObjectType = 'N'
                  AND EndTime.NetworkNode = StartTime.NetworkNode
              ORDER BY EndTime.EventTime)) AS DurationInMinutes


      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
      WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')
      ORDER BY StartTime.EventTime DESC

      How can I filter this script with a specific Node and the Duration is null?

        • Re: Filtering in SQL
          lchance

          i think i got the NODE piece but i'm still working on the Duration. the percent part is wildcard of course. remove it and use your exact node name.

          i'm not a SQL person but i'll give anything a try...

           

          SELECT
              StartTime.EventTime,
             
              Nodes.Caption,
              DATEDIFF(Mi, StartTime.EventTime,
              (SELECT TOP 1
                  EventTime
                  FROM Events AS Endtime
                  WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
                      AND EndTime.NetObjectType = 'N'
                      AND EndTime.NetworkNode = StartTime.NetworkNode
                  ORDER BY EndTime.EventTime)) AS DurationInMinutes


          FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
          WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')

          AND Nodes.Caption LIKE 'MyNodesNamed%'
          ORDER BY StartTime.EventTime DESC