Help with date

valdemirs

unread,

Dec 21, 2009, 6:22:19 AM12/21/09

to

Hi:
Please, is there any way to summarize this sql command below to show
me only one data per day ?
Actually it´s show me 24 data ( one per hour):

SELECT TOP 10000 Nodes.Caption AS NodeName,
APM_AlertsAndReportsData.ComponentName AS Component_Name,
APM_ResponseTime.StatisticData AS StatisticsData,
APM_ResponseTime.DateTime AS DateTime

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())-1,0) AND DateTime
<dateadd(mm,datediff(mm,0,getdate()),0) )

AND
(
(Nodes.Caption = 'SERV-ABP3') AND
(
(APM_AlertsAndReportsData.ComponentName = 'swap')

))

serv-abp3 swap 1 2009-11-26 00:00:00.000
serv-abp3 swap 1 2009-11-26 01:00:00.000
serv-abp3 swap 1 2009-11-26 02:00:00.000
serv-abp3 swap 1 2009-11-26 03:00:00.000
serv-abp3 swap 1 2009-11-26 04:00:00.000
serv-abp3 swap 1 2009-11-26 05:00:00.000
serv-abp3 swap 1 2009-11-26 06:00:00.000
serv-abp3 swap 1 2009-11-26 07:00:00.000
serv-abp3 swap 1,41666666666667 2009-11-26 08:00:00.000
serv-abp3 swap 2 2009-11-26 09:00:00.000
serv-abp3 swap 2 2009-11-26 10:00:00.000
serv-abp3 swap 2 2009-11-26 11:00:00.000
serv-abp3 swap 2 2009-11-26 12:00:00.000
serv-abp3 swap 2 2009-11-26 13:00:00.000
serv-abp3 swap 2 2009-11-26 14:00:00.000
serv-abp3 swap 2 2009-11-26 15:00:00.000
serv-abp3 swap 2 2009-11-26 16:00:00.000
serv-abp3 swap 1,83333333333333 2009-11-26 17:00:00.000
serv-abp3 swap 1 2009-11-26 18:00:00.000
serv-abp3 swap 1 2009-11-26 19:00:00.000
serv-abp3 swap 1 2009-11-26 20:00:00.000
serv-abp3 swap 1 2009-11-26 21:00:00.000
serv-abp3 swap 1 2009-11-26 22:00:00.000
serv-abp3 swap 1 2009-11-26 23:00:00.000

Plamen Ratchev

unread,

Dec 21, 2009, 1:37:48 PM12/21/09

to

It is not very clear how you want to group your data (for example, do you want to summarize statistic data). Here is
generic form to get rid of the time portion (by setting it to midnight for all dates) and group by all columns, which
will eliminate the duplicate lines. You may want to use the SUM aggregate function for the StatisticData column and
remove it from the GROUP BY clause.

SELECT TOP 10000 Nodes.Caption AS NodeName,
APM_AlertsAndReportsData.ComponentName AS Component_Name,
APM_ResponseTime.StatisticData AS StatisticsData,

DATEADD(DAY, DATEDIFF(DAY, 0, APM_ResponseTime.[DateTime]), 0) AS [Date]
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, CURRENT_TIMESTAMP)-1, 0)
AND [DateTime] < DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)
AND Nodes.Caption = 'SERV-ABP3'
AND APM_AlertsAndReportsData.ComponentName = 'swap'
GROUP BY Nodes.Caption,
APM_AlertsAndReportsData.ComponentName,
APM_ResponseTime.StatisticData,
DATEADD(DAY, DATEDIFF(DAY, 0, APM_ResponseTime.[DateTime]), 0);

--
Plamen Ratchev
http://www.SQLStudio.com

Erland Sommarskog

unread,

Dec 21, 2009, 4:59:39 PM12/21/09

to

valdemirs (vald...@gmail.com) writes:
> Please, is there any way to summarize this sql command below to show
> me only one data per day ?

To show data once per day:

SELECT Nodes.Caption AS NodeName,
APM_AlertsAndReportsData.ComponentName AS Component_Name,
AVG(APM_ResponseTime.StatisticData) AS StatisticsData,
convert(char(10), APM_ResponseTime.DateTime, 112) AS DateTime
FROM ...
GROUP BY Nodes.Caption, APM_AlertsAndReportsData.ComponentName,
convert(char(10), APM_ResponseTime.DateTime, 112)

To group by the hour, just change char(10) to char(13)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.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/prodinfo/previousversions/books.mspx