This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Help with SQL code

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

  • 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?