1 Reply Latest reply on Jan 13, 2010 12:12 AM by Steven Klassen

    Help with SQL code

    valdemirs

      Please help with this sql code:

      ( I need a code to show me only 1 data per month):

      SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
      Nodes.NodeID AS NodeID,
      Nodes.Caption AS NodeName,
      APM_AlertsAndReportsData.ComponentName AS Component_Name,
      AVG(APM_ResponseTime.StatisticData) AS process_run_queue

      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) >= '07:00') AND
        (Convert(Char,DateTime,108) <= '19:00')

      )

      AND
       
      (
        (Nodes.Grupo_Funcao = 'Ipiranga') AND
        (APM_AlertsAndReportsData.ComponentName = 'process_run_queue') AND
        (
        (Nodes.Caption = 'serv-sn-mtz')
      )
      )


      GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
      Nodes.NodeID, Nodes.Caption, APM_AlertsAndReportsData.ComponentName


      ORDER BY SummaryDate ASC

       

      I need a code to show me only 1 data per month, but I´m getting this output:

       

      Date "Node ID" Node Component_Name process_run_queue
      14-Dec-09 1449 SERV-SN-mtz process_run_queue 1.63701388888889
      15-Dec-09 1449 SERV-SN-mtz process_run_queue 1.72597222222222
      16-Dec-09 1449 SERV-SN-mtz process_run_queue 2.26494318181818
      17-Dec-09 1449 SERV-SN-mtz process_run_queue 1.93256944444444
      18-Dec-09 1449 SERV-SN-mtz process_run_queue 1.55555555555556
      21-Dec-09 1449 SERV-SN-mtz process_run_queue 1.45410984848485
      22-Dec-09 1449 SERV-SN-mtz process_run_queue 1.72458333333333
      23-Dec-09 1449 SERV-SN-mtz process_run_queue 1.58784722222222
      24-Dec-09 1449 SERV-SN-mtz process_run_queue 0.662013888888889
      25-Dec-09 1449 SERV-SN-mtz process_run_queue 0.349305555555556
      28-Dec-09 1449 SERV-SN-mtz process_run_queue 1.60020833333333
      29-Dec-09 1449 SERV-SN-mtz process_run_queue 2.34291666666667
      30-Dec-09 1449 SERV-SN-mtz process_run_queue 1.63569444444444
      31-Dec-09 1449 SERV-SN-mtz process_run_queue 0.824791666666667

        • Re: Help with SQL code
          Steven Klassen

          The data type in your select that you're aliasing as SummaryDate is being handled in its default format. If you really mean to summarize by month only (and then get one value per month) you'll want to use datepart() to extract the month like you're doing in the 'where' clause. I take it you didn't quite this query then?