How to Exract SQL minute data to houly data in C#?

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

RowDateTime Meter 1 Meter 2 Meter 3
25/05/2006 02:49:00 1220 450 489
25/05/2006 02:50:00 1223 470 500
25/05/2006 02:51:00 1227 490 511
25/05/2006 02:52:00 1230 510 522
25/05/2006 02:53:00 1233.5 530 533
25/05/2006 02:54:00 1236.9 550 544
25/05/2006 02:55:00 1240.3 570 555
25/05/2006 02:56:00 1243.7 590 566

I want to make a C# code or SQL 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 :-

RowDateTime Meter 1 Meter 2 Meter 3
25/05/2006 03:00:00 9854.4 4160 527.5
25/05/2006 04:00:00 8634.4 3710 533
25/05/2006 05:00:00 7411.4 3240 538.5

Your help will be highly appreciated.

[13009 byte] By [Sibusiso] at [2007-12-22]

«« Dynamic images for pushpins
»» Unable to determine the workspace

# 1

Try this. I leave the part about figuring the time display out to you.

select sum(meter1), sum(meter2), sum(meter3)
from fc
group by
datepart(yy,rowdatetime),
datepart(mm,rowdatetime),
datepart(dd,rowdatetime),
datepart(hh,rowdatetime)

ZahirJ at 2007-8-30 >

top of Msdn Tech,Visual C#,Visual C# General...

# 2

Will this work for a million rows? Will it not be summing over all 1 million rows?

If this doesn't work, then perhaps you'd have to instead select all rows that fall within a certain hour, sum those, and then move on to the next hour until reaching your end date.

ShellShock at 2007-8-30 >

# 3

ShellShock wrote:

Will this work for a million rows? Will it not be summing over all 1 million rows?
If this doesn't work, then perhaps you'd have to instead select all rows that fall within a certain hour, sum those, and then move on to the next hour until reaching your end date.

I agree with ShellShock, thats what I need.

Your help will be highly apprecaited.

Sibusiso at 2007-8-30 >

# 4

I got it, thanks guys. Here is the solution I used :-

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)

Sibusiso at 2007-8-30 >

Visual C# Hot Topic

Visual C# New Topic

Visual C#

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