Aggregate quarter hours in hours

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

Transact-SQL (2008)

Aggregate quarter hours in hours

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-18 : 10:57:47

Hello all,
I have a table with hour quarter (quarter of an hour) values,
something like this:

Value - DateQuarter
200 - 2012-01-01 00:00:00.000
230 - 2012-01-01 00:15:00.000
100 - 2012-01-01 00:30:00.000
120 - 2012-01-01 00:45:00.000
200 - 2012-01-01 01:00:00.000
230 - 2012-01-01 01:15:00.000
110 - 2012-01-01 01:30:00.000
120 - 2012-01-01 01:45:00.000
...

Now I need to sum the 4 values for each hour in a single record, to obtain:

650 - 2012-01-01 00:00:00.000
660 - 2012-01-01 01:00:00.000
...

that I can write them in another table.

How can I write this query?

Thanks in advance.

Luigi

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-18 : 11:06:17

This should do what you want:

select 
	ValueSum	= sum(Value),
	[DateHour]	= dateadd(hh,datediff(hh,0,DateQuarter),0)
from
	MyTable
group by
	dateadd(hh,datediff(hh,0,DateQuarter),0)
order by
	dateadd(hh,datediff(hh,0,DateQuarter),0)

More info on the links below:
Start of Time Period Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-18 : 11:30:06

Thank you very much Michael.

Luigi

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-19 : 10:10:52

Hi Michael,
just a little modification.
How can I change this query if my hour time start at 15min instead of 00min?

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 10:35:11

see

http://visakhm.blogspot.in/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-19 : 15:02:13

I cannot adapt your article with my example Visakh.

Luigi

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2012-12-19 : 16:41:05

[code]
select
ValueSum = sum(Value),
[DateHour] = dateadd(minute, 15, dateadd(Hour, datediff(Hour, 0, DateQuarter),0))
from
MyTable
group by
dateadd(minute, 15, dateadd(Hour, datediff(Hour, 0, DateQuarter),0))
order by
dateadd(minute, 15, dateadd(Hour, datediff(Hour, 0, DateQuarter),0))
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-19 : 18:51:24

quote:
Originally posted by Ciupaz

Hi Michael,
just a little modification.
How can I change this query if my hour time start at 15min instead of 00min?

Luigi

This produces correct results over the datetime range of:
1753-01-01 00:15:00.000 thru 9999-12-31 23:35:59.997

select
	a.DT,
	[DateHourStart15] =
	dateadd(hh,datediff(hh,0,dateadd(mi,-15,a.DT)),'00:15:00')
from
	(  -- Test Data
	select dt = getdate()	union all
	--select dt = '17530101 00:14:59.997'	union all
	select dt = '17530101 00:15:00.000'	union all
	select dt = '18991219 00:14:59.997'	union all
	select dt = '18991219 00:15:00.000'	union all
	select dt = '20121219 00:14:59.997'	union all
	select dt = '20121219 00:15:00.000'	union all
	select dt = '20121219 01:14:59.997'	union all
	select dt = '20121219 01:15:00.000'	union all
	select dt = '20121219 23:14:59.997'	union all
	select dt = '20121219 23:15:00.000'	union all
	select dt = '20121220 00:14:59.997'	union all
	select dt = '20121220 00:15:00.000'	union all
	select dt = '99991231 23:14:59.997'	union all
	select dt = '99991231 23:15:00.000'	union all
	select dt = '99991231 23:59:59.997'
	) a
order by
	a.DT

Results:

DT                      DateHourStart15
----------------------- -----------------------
1753-01-01 00:15:00.000 1753-01-01 00:15:00.000
1899-12-19 00:14:59.997 1899-12-18 23:15:00.000
1899-12-19 00:15:00.000 1899-12-19 00:15:00.000
2012-12-19 00:14:59.997 2012-12-18 23:15:00.000
2012-12-19 00:15:00.000 2012-12-19 00:15:00.000
2012-12-19 01:14:59.997 2012-12-19 00:15:00.000
2012-12-19 01:15:00.000 2012-12-19 01:15:00.000
2012-12-19 18:43:33.757 2012-12-19 18:15:00.000
2012-12-19 23:14:59.997 2012-12-19 22:15:00.000
2012-12-19 23:15:00.000 2012-12-19 23:15:00.000
2012-12-20 00:14:59.997 2012-12-19 23:15:00.000
2012-12-20 00:15:00.000 2012-12-20 00:15:00.000
9999-12-31 23:14:59.997 9999-12-31 22:15:00.000
9999-12-31 23:15:00.000 9999-12-31 23:15:00.000
9999-12-31 23:59:59.997 9999-12-31 23:15:00.000

CODO ERGO SUM

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-20 : 04:03:28

Thank you Michael.

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources