aggregate values and use datepart? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

aggregate values and use datepart?

Author

Topic

Balzary
Starting Member

1 Post

Posted - 2006-01-20 : 11:02:32

Hi I have a table with A LOT of rows in who I would like to slice with help of datepart or something like that.

This table has a column CounterDateTime and an other CounterValue and some more.
I would like to create a new table with aggregate values of CounterValue based on minutes in the CounterDateTime column.

I created this SQL statement for this issue
select serverInstanceID,CounterID,max(CounterDateTime) as 'CounterDateTime',AVG(CounterValue) as 'CounterValue',AggregateDays into tempppp from perf.counterdata
group by serverInstanceID,CounterID,datepart(mi,CounterDateTime) ,datepart(hh,CounterDateTime),datepart(dd,CounterDateTime),datepart(mm,CounterDateTime),datepart(yy,CounterDateTime),Aggregatedays

The original table has values like this in it.

(Counterdatetime has the format YYYY-MM-DD HH:MI:SS:MS)

sID CID CounterDateTime CounterValue Agg

1 1 2006-01-17 09:34:28.473 99.99904 1
1 1 2006-01-17 09:34:30.473 14.4525774 1
1 1 2006-01-17 09:35:32.473 12.2552984 1
1 1 2006-01-17 09:36:34.473 12.1576428 1
1 1 2006-01-17 09:36:36.473 15.0873465 1
1 1 2006-01-17 09:36:38.473 15.8197712 1
1 1 2006-01-17 09:37:40.473 18.5541687 1
1 1 2006-01-17 09:37:45.413 18.9541687 1

This SQL Statement seems to do what I want. Meaning create a new table with the average values within 1 minute on 1 row...

Can anyone please tell me if this is correct?
Is there any smarter way to do this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-20 : 11:48:31

This code shows a much simpler method to convert time to whole minutes.


select
	[DateTime down to Minute] = 
	dateadd(mi,datediff(mi,0,CounterDateTime),0)
from
(
select CounterDateTime = 
	convert(datetime,'2006-01-17 09:34:28.473')
union all
select CounterDateTime = 
	convert(datetime,'2006-01-17 09:35:32.473')
union all
select CounterDateTime = 
	convert(datetime,'2006-01-17 09:37:40.473')
) a



DateTime down to Minute                                
------------------------------------------------------ 
2006-01-17 09:34:00.000
2006-01-17 09:35:00.000
2006-01-17 09:37:00.000

(3 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources