Grouping Minutes into Hours - 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 2000 Forums

SQL Server Development (2000)

Grouping Minutes into Hours

Author

Topic

ds9
Starting Member

38 Posts

Posted - 2006-04-26 : 17:27:27

I have a table with data every 15 minutes and I want to summarize that data on houlry basis:
I have this:
DATE A
10042006 02:00 10
10042006 02:15 20
10042006 02:30 5
10042006 02:45 10
10042006 03:00 15
10042006 03:15 20
I want to have this:
DATA A
10042006 02:00 45 (10+20+5+10)
10042006 03:00 35 (15+10)

I'm trying to make a DTS that performs this task but don't know how realy...

Many thanks
DS9

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 17:50:40

[code]

select
Hour = dateadd(hh,datediff(hh,0,c.MyDate),0),
MyHourlyTotal = sum(c.MyDataColumn)
from
MyTable c
group by
dateadd(hh,datediff(hh,0,c.MyDate),0)
order by
dateadd(hh,datediff(hh,0,c.MyDate),0)
[/code]

CODO ERGO SUM

ds9
Starting Member

38 Posts

Posted - 2006-04-27 : 11:06:51

It worked! thanks a lot!
Now I am into a new dificultie: From the Hourly values and for each day, I want to isolate the lines that have the MAX value. The example:
DATA A
10042006 02:00 10
10042006 03:00 30
10042006 04:00 5
10042006 05:00 10
10042006 06:00 15
10042006 07:00 20
11042006 02:00 10
11042006 03:00 20
11042006 04:00 5
11042006 05:00 10
11042006 06:00 35
11042006 07:00 20

The output would be

DATA A
10042006 03:00 30
11042006 06:00 35

The simple grouby clause will isolate every hour. I need somehow to group by day but show the hour of the maximum value..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 18:04:05

[code]
select day, hour, value
from yourtable t
where value = (select max(value) from yourtable x where x.day = t.day)
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-27 : 21:25:47

If you need to group your times at various levels, you can use the functions on these two links. You can create the functions and use them directly, or look at the code in the functions to see how to round off datetimes to various levels.

Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
dbo.F_START_OF_WEEK(@DATE,@WEEK_START_DAY)

Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
dbo.F_START_OF_CENTURY( @DAY )
dbo.F_START_OF_DECADE( @DAY )
dbo.F_START_OF_YEAR( @DAY )
dbo.F_START_OF_QUARTER( @DAY )
dbo.F_START_OF_MONTH( @DAY )
dbo.F_START_OF_DAY( @DAY )
dbo.F_START_OF_HOUR( @DAY )
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
dbo.F_START_OF_MINUTE( @DAY )
dbo.F_START_OF_SECOND( @DAY )

CODO ERGO SUM

ds9
Starting Member

38 Posts

Posted - 2006-05-02 : 12:49:57

Hi there

Thanks for your thoughts. The time functions are really helpfull.
I still can't get the output I need. Note that I don't want (or don't need) to Group by values.
I only want to choose the maximum per each day, and get, for each day the date(dat+hour) where that maximum was found. I can groupby day using the functions and get the maximum, but the date field will show the day without the time of the maximum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources