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 - DateQuarter200 - 2012-01-01 00:00:00.000230 - 2012-01-01 00:15:00.000100 - 2012-01-01 00:30:00.000120 - 2012-01-01 00:45:00.000200 - 2012-01-01 01:00:00.000230 - 2012-01-01 01:15:00.000110 - 2012-01-01 01:30:00.000120 - 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.000660 - 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 MyTablegroup 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 Functionshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755Date/Time Info and Script Linkshttp://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 |
|
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 MyTablegroup 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.997select 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' ) aorder by a.DT Results:DT DateHourStart15----------------------- -----------------------1753-01-01 00:15:00.000 1753-01-01 00:15:00.0001899-12-19 00:14:59.997 1899-12-18 23:15:00.0001899-12-19 00:15:00.000 1899-12-19 00:15:00.0002012-12-19 00:14:59.997 2012-12-18 23:15:00.0002012-12-19 00:15:00.000 2012-12-19 00:15:00.0002012-12-19 01:14:59.997 2012-12-19 00:15:00.0002012-12-19 01:15:00.000 2012-12-19 01:15:00.0002012-12-19 18:43:33.757 2012-12-19 18:15:00.0002012-12-19 23:14:59.997 2012-12-19 22:15:00.0002012-12-19 23:15:00.000 2012-12-19 23:15:00.0002012-12-20 00:14:59.997 2012-12-19 23:15:00.0002012-12-20 00:15:00.000 2012-12-20 00:15:00.0009999-12-31 23:14:59.997 9999-12-31 22:15:00.0009999-12-31 23:15:00.000 9999-12-31 23:15:00.0009999-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 |
|
|
|