SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Aggregate quarter hours in hours		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/18/2012 :  10:57:47  Show Profile  Reply with Quote
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)

USA
6997 Posts
Posted - 12/18/2012 :  11:06:17  Show Profile  Reply with Quote
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
Edited by - Michael Valentine Jones on 12/18/2012 11:09:34
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/18/2012 :  11:30:06  Show Profile  Reply with Quote
Thank you very much Michael.

Luigi
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/19/2012 :  10:10:52  Show Profile  Reply with Quote
Hi Michael,
just a little modification.
How can I change this query if my hour time start at 15min instead of 00min?

Luigi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 12/19/2012 :  10:35:11  Show Profile  Reply with Quote
see

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/19/2012 :  15:02:13  Show Profile  Reply with Quote
I cannot adapt your article with my example Visakh.

Luigi
Go to Top of Page

ScottPletcher
Yak Posting Veteran

USA
79 Posts
Posted - 12/19/2012 :  16:41:05  Show Profile  Reply with Quote 

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))
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 12/19/2012 :  18:51:24  Show Profile  Reply with Quote
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
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/20/2012 :  04:03:28  Show Profile  Reply with Quote
Thank you Michael.

Luigi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000