0 Replies Latest reply on Feb 10, 2011 10:38 AM by sw42029

    Problem with summing a column on outage report

    sw42029

      I have a report that runs at the first of every month to give me an outage report for the previous month.  This report is currently working and gives me each locations name, event down time, event up time, and then an outage duration in minutes.  

       

      Currently I'm trying to get this report to give me a summary of all of the outages by means of summing the Outage in minutes column.  All I want to see (or rather my management) is a total number in minutes of down time last month.  Unfortunately I've had very little luck in doing so.  When I try to sum OutageDurationInMinutes it gives me an error

       

      "SQL Error:

      Cannot perform aggregate function on a n expression containing an aggregate or a subquery."
      .
      .
      I've tried several times to break the functions out so that I'm not performing an aggregate on an aggregate but can't seem to get it.  
      I'm hoping someone with more SQL experience can help!
      Thank you for the help in advance!
      Below are my statements:

       

       

       

      SELECT top 1000

      Nodes.Caption,

      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 UpEventTime, 

       

       

      DATEDIFF(Minute, 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 OutageDurationInMinutes

       

       

      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

       

       

      WHERE 

      (DATEPART(month, EventTime) = (DATEPART(month, getdate())) -1 ) AND

      NOT (Nodes.Caption LIKE '%Test%') AND 

      (StartTime.EventType = 1) AND 

        (

         (Nodes.MachineType LIKE '%1720%') OR 

         (Nodes.MachineType LIKE '%1760%') OR 

         (Nodes.MachineType LIKE '%1841%') OR 

         (Nodes.MachineType LIKE '%1861%') OR 

         (Nodes.MachineType = 'Cisco') OR 

         (Nodes.MachineType LIKE '%2811%') OR 

         (Nodes.MachineType LIKE '%Cisco ASA 5505%') OR 

         (Nodes.MachineType LIKE '%Cisco 871%')

      )

      ORDER BY 1 ASC, 2 ASC, 3 ASC