Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

How to Exract SQL minute data to houly data?

Hi

I have a SQL table that has data filled with million records and the date is in minutes it looks like :-

RowDateTimeMeter 1Meter 2Meter 3
25/05/2006 02:49:001220450489
25/05/2006 02:50:001223470500
25/05/2006 02:51:001227490511
25/05/2006 02:52:001230510522
25/05/2006 02:53:001233.5530533
25/05/2006 02:54:001236.9550544
25/05/2006 02:55:001240.3570555
25/05/2006 02:56:001243.7590566

I want to make a query to the above table and convert the data to houlry by summing Meter1,Meter 2 and Meter 3 to be the average. I want to import all the hourly data to a new table that will look like :-

RowDateTimeMeter 1Meter 2Meter 3
25/05/2006 03:00:009854.44160527.5
25/05/2006 04:00:008634.43710533
25/05/2006 05:00:007411.43240538.5

Your help will be highly appreciated.

[12994 byte] By [Sibusiso] at [2007-12-22]
«« List the domain name
»» reflection - CustomAttributeBuilder
# 1

This formula: DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) will remove the minute and seconds from a datetime. Using this as your group by should solve your problem.

Select DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime,
Avg(Meter1) As Meter1,
Avg(Meter2) As Meter2,
Avg(Meter3) As Meter3
From TableName
Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)

mastros at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2
Thanks a lot, thats what I needed
Sibusiso at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified