Group by hour - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Group by hour

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-08-31 : 15:25:07

I am tracking performance counters in table.
Suppose if counters are hitting continously every sec above threshold value then I want to know Time and Values.

For Example:
Lazy writes/sec

ColA(int) ColA(Datetime)
40 '2009-08-17 06:00:00'
50 '2009-08-17 06:30:00'
60 '2009-08-17 06:40:00'
20 '2009-08-17 07:00:00'
30 '2009-08-17 07:30:00'
67 '2009-08-17 08:00:00'

I want to track this in hourly basis. Any great ideas. Table has millions of records.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-08-31 : 16:04:52

Any luck??

How to get max value by hourly?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 16:11:54

Here's one way to group by hour. You aren't planning on running this over the entire table are you ???


select dateadd(hour, datediff(hour, 0, ColB), 0) as HourStarting 
       ,max(ColA) as MaxForHour
from   
       (
       select 40 ColA, '2009-08-17 06:00:00' as ColB union all
       select 50, '2009-08-17 06:30:00' union all
       select 60, '2009-08-17 06:40:00' union all
       select 20, '2009-08-17 07:00:00' union all
       select 30, '2009-08-17 07:30:00' union all
       select 67, '2009-08-17 08:00:00'
       ) as [YourTable]

group by datediff(hour, 0, ColB)

OUTPUT:

HourStarting            MaxForHour
----------------------- -----------
2009-08-17 06:00:00.000 60
2009-08-17 07:00:00.000 30
2009-08-17 08:00:00.000 67

EDIT:
added executable code w/output

Be One with the Optimizer
TG

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-08-31 : 16:20:47

Thanks. I will filter with where clause.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-08-31 : 16:55:40

How can I group by Every 5 mins? Can I do it?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-31 : 17:08:59

[code]select dateadd(minute, datediff(minute, 0, ColB)/5*5, 0) as FiveMinuteStarting
,max(ColA) as MaxForFiveMinute
from
(
select 40 ColA, '2009-08-17 06:00:00' as ColB union all
select 50, '2009-08-17 06:30:00' union all
select 60, '2009-08-17 06:40:00' union all
select 20, '2009-08-17 07:00:00' union all
select 30, '2009-08-17 07:30:00' union all
select 67, '2009-08-17 08:00:00'
) as [YourTable]
group by dateadd(minute, datediff(minute, 0, ColB)/5*5, 0)[/code]Warning: not tested.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-08-31 : 17:17:47

Excellent. Thanks.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-09-28 : 10:09:35

I want to group above records by 5 mins. I have 60 days worth of records in the table and I want to pick records only from 10:00 A.M to 6:00 PM for all 60 days records.

How can i select from 10:00 AM to 6:00 PM and again group by 5 mins? Thanks in advance.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-09-28 : 10:45:37

Any suggestions?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 11:03:44

Include this in the WHERE clause:

where datediff(hour, dateadd(day, datediff(day, 0, ColB), 0), ColB) between 10 and 17

Be One with the Optimizer
TG

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-09-28 : 11:12:15

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources