2 Replies Latest reply on Nov 18, 2014 4:51 PM by njoylif

    Report SQL help

    eric.pfeifer

      All.. I am new to SQL writing and need help with this custom sql that was already written in report writer.

       

      Request - To report on sites for the following 30 days where we filter out hours 11pm to 6am mon - friday and all weekends (sat/sun) - so we are only reporting on business hours.

       

      Sql is below

       

       

      SELECT Nodes.NodeID, Nodes.CustomerID as [Site Number], Nodes.CustomerName AS [Site Name], AVG(FMC_AvailabilityByDays.Availability)
                        AS [Average Availability], ISNULL(FMC_OutagesLast30Days.Outages, 0) AS '# Outages',
                        ISNULL(FMC_OutageDurationLast30Days.TotalOutageDuration, 0) AS 'Total Outage Duration'
      FROM     Nodes INNER JOIN
                        FMC_AvailabilityByDays ON Nodes.NodeID = FMC_AvailabilityByDays.NodeID LEFT OUTER JOIN
                        FMC_OutageDurationLast30Days ON Nodes.NodeID = FMC_OutageDurationLast30Days.nodeid LEFT OUTER JOIN
                        FMC_OutagesLast30Days ON Nodes.NodeID = FMC_OutagesLast30Days.NodeID
      WHERE (FMC_AvailabilityByDays.datetime BETWEEN DATEADD(day, - 30, DateDiff(dd, 0, getdate())) AND DATEADD(day, - 1, DateDiff(dd, 0, getdate()))) AND
                        ((Nodes.LocationType like '%Clinic%') OR (Nodes.LocationType = 'Dialysis Location')) AND (Nodes.DeviceType = 'Router') AND (Nodes.Application IS NULL) AND
                        (Nodes.UnManaged = 0)

      GROUP BY Nodes.CustomerName, Nodes.CustomerID, Nodes.NodeID, FMC_OutageDurationLast30Days.TotalOutageDuration, FMC_OutagesLast30Days.Outages

      ORDER BY [Average Availability]

        • Re: Report SQL help
          njoylif

          look in content share section for reports and find one with business hours;

          you should then be able to add that section of where clause to your report.

            • Re: Report SQL help
              njoylif

              ah, this is a little more tricky being the last 30 days...

              add something like:

              AND

                 (

                 LTRIM(MONTH(FMC_AvailabilityByDays.datetime)) = (LTRIM(MONTH(getdate())) -1)

                 AND

                 DATEPART(dw, FMC_AvailabilityByDays.datetime) between 2 and 6  --- gives monday through friday

                 AND

                  (   --- gives btn 6a and 11p

                      t.DateTime BETWEEN

                          CAST(Convert(Char(10), FMC_AvailabilityByDays.datetime, 101) as smalldatetime) + ' 06:00'

                          AND

                          CAST(Convert(Char(10), FMC_AvailabilityByDays.datetime, 101) as smalldatetime) + ' 23:00'

                  )

                 )