Hi:
Please, could you help me with this SQL query ?

I need get data from last 3 months,
but it´s shows me only January's data.
(Query doesn´t get data from november and december/2009)

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

valdemirs (valdemirs (AT) gmail (DOT) com) writes:
Have you verified that there is data from November and December 2009?
And does this data also include a time portion between 08:00 and 17:00?

And is the data type of the DateTime column datetime? Or is it something
else?

By the way, how many rows does your query return? The full 10000?


As lot easier way to write this is:

convert(datetime, convert(char(6), DateTime, 112) + '01')



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx