18 Replies Latest reply on Mar 24, 2017 9:15 AM by Abdhija Sharma

    I need to generate a report that sorts the result using multiple conditions.

    fazl azeem

      The report is of interface utilization, and the sorting need to be done on following conditions.

      1. Sort the report w.r.t Monday-Friday(Sat and Sun excluded)

      2. Sort the report w.r.t business hours i.e 9am -5pm

      3. Sort the result on monthly basis.

       

      All need to be in same report, which includes only business days and business hours and on monthly basis.

      I really need help with this. Will be much obliged to get the help.

      Below is a SQL query that i received from someone, but i do not know how it works or what will be the result because the query is generating error.

       

      declare @periodbegin datetime

      set @periodbegin='17-05-2014'

      declare @periodEnd datetime

      set @periodEnd='18-05-2014'

       

      SELECT

          StartTime.EventTime,

          Nodes.Caption,

       

          StartTime.Message,

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

       

          DATEDIFF(Mi, StartTime.EventTime,

          (SELECT TOP 1

              EventTime

              FROM Events AS Endtime

              WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 14

                  AND EndTime.NetObjectType = 'N'

                  AND EndTime.NetworkNode = StartTime.NetworkNode

              ORDER BY EndTime.EventTime))  AS OutageDurationInMinutesByPower

       

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

      WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND

      eventtime between ('17/07/2014' and '18/07/2014')

      ORDER BY Nodes.Caption ASC

       

       

      Best Regards.