1 Reply Latest reply on Mar 2, 2010 6:44 AM by njoylif

    SQL query - Last 3 months

    valdemirs

      Please, can you help me with this sql query?

      (This query should to show all data from last 3 months, but it´s shows me data from last month only).

       

      SELECT TOP 10000 CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth,
      Nodes.NodeID AS NodeID,
      Nodes.Caption AS NodeName,
      Nodes.IP_Address AS IP_Address,
      APM_AlertsAndReportsData.ComponentName AS Component_Name,
      AVG(APM_ResponseTime.StatisticData) AS AVERAGE_of_StatisticsData

      FROM
      (Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID =
      APM_AlertsAndReportsData.NodeId)) INNER JOIN APM_ResponseTime ON
      (APM_AlertsAndReportsData.ComponentId = APM_ResponseTime.ComponentID)

      WHERE

      ( DateTime >= dateadd(mm,datediff(mm,0,getdate())-3,0) AND DateTime
      <dateadd(mm,datediff(mm,0,getdate()),0) )

      AND
      (
         (DATEPART(weekday, DateTime) <> 1) AND
         (DATEPART(weekday, DateTime) <> 7) and
      (Convert(Char,DateTime,108) >= '08:00') AND
        (Convert(Char,DateTime,108) <= '17:00')

      )

       AND
      (
        (Nodes.Grupo_Funcao = 'Ipiranga') AND
        (
         (Nodes.Caption = 'serv-abp3') OR
         (Nodes.Caption = 'notesmatriz02'))
      )

      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' +
      LTRIM(YEAR(DateTime)), 101),
      Nodes.NodeID, Nodes.Caption, Nodes.IP_Address,
      APM_AlertsAndReportsData.ComponentName

      ORDER BY SummaryMonth ASC

        • Re: SQL query - Last 3 months
          njoylif

          that gets REAL dicey.  I'm thinking you are only getting the last month (should be 2 now) because it is Feb/March.  You are asking for the last 3 months, not including this one.  as it is march, it should give you Feb '10, Jan '10 and the way the SQL is written, Dec '10 - which there is no data match for.

          this type of SQL gets really complicated when you have to account for beginning of the year so it rolls back to the last year for reports run in Jan/Feb/Mar.

          Hope that helps with the problem anyways.