how to aggregate interval data to hourly values?

Question

Post reply

how to aggregate interval data to hourly values?

smart_analyst

Ten Centuries

Points: 1037
More actions
May 12, 2014 at 9:38 pm

#396740

Hi,
I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000
I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. Can anybody advise how should I tweak this query to sum the interval values and display it?
SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId
Current Output
MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

SELECT MeterID, ReadingDay = CONVERT(DATE,ReadingDate), ReadingHour = HOUR(ReadingDate), SUM(Reading)

FROM

(

SELECT r.MeterId, r.ReadingDate, r.Reading

FROM MeterReading r, MeterDetail d, Building b

where r.MeterId = d.MeterId

and d.BuildingId = b.BuildingId

and b.BuildingName like '%182%'

and r.ReadingDate between '2014-01-01'and '2014-01-10'

) tmp

GROUP BY MeterID,CONVERT(DATE,ReadingDate),HOUR(ReadingDate)

order by MeterId

I haven't been able to test the code, since you did not provide table DDL and sample data.

(check the first link in my signature on how to ask questions on a forum)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Megha P SSCommitted Points: 1976 More actions · Answer 2

Don't know performance wise it is good or bad..but i come up with below code

create Table #temp

(MeteriD INT,

ReadingDate DATETIME,

Reading INT

)

INSERT #temp

SELECT 3969,'1/01/2014 0:00', 0

UNION ALL

SELECT 3969,'1/01/2014 0:15', 0

UNION ALL

SELECT 3969,'1/01/2014 0:30', 0

UNION ALL

SELECT 3969,'1/01/2014 0:45', 0

UNION ALL

SELECT 3969,'1/01/2014 1:00', 0

UNION ALL

SELECT 3969,'1/01/2014 1:15', 1

UNION ALL

SELECT 3969,'1/01/2014 1:30', 0

UNION ALL

SELECT 3969,'1/01/2014 1:45', 0

UNION ALL

SELECT 3969,'1/01/2014 2:00', 0

UNION ALL

SELECT 3969,'1/01/2014 2:15', 0

UNION ALL

SELECT 3969,'1/01/2014 2:30', 0

UNION ALL

SELECT 3969,'1/01/2014 2:45', 0

UNION ALL

SELECT 3969,'1/01/2014 3:00', 0

;WITH CTE AS

(

SELECTDENSE_RANK ()over(order by DATEPART(hh,ReadingDate)) Number,

MeteriD,

ReadingDate,

Reading

FROM #temp

)

SELECT MeteriD,SUM(Reading) Reading ,MIN(ReadingDate) ReadingDate from CTE Group by Number,MeterID

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Megha P (5/13/2014)
Don't know performance wise it is good or bad..but i come up with below code
create Table #temp
(MeteriD INT,
ReadingDate DATETIME,
Reading INT
)
INSERT #temp
SELECT 3969,'1/01/2014 0:00', 0
UNION ALL
SELECT 3969,'1/01/2014 0:15', 0
UNION ALL
SELECT 3969,'1/01/2014 0:30', 0
UNION ALL
SELECT 3969,'1/01/2014 0:45', 0
UNION ALL
SELECT 3969,'1/01/2014 1:00', 0
UNION ALL
SELECT 3969,'1/01/2014 1:15', 1
UNION ALL
SELECT 3969,'1/01/2014 1:30', 0
UNION ALL
SELECT 3969,'1/01/2014 1:45', 0
UNION ALL
SELECT 3969,'1/01/2014 2:00', 0
UNION ALL
SELECT 3969,'1/01/2014 2:15', 0
UNION ALL
SELECT 3969,'1/01/2014 2:30', 0
UNION ALL
SELECT 3969,'1/01/2014 2:45', 0
UNION ALL
SELECT 3969,'1/01/2014 3:00', 0
;WITH CTE AS
(
SELECTDENSE_RANK ()over(order by DATEPART(hh,ReadingDate)) Number,
MeteriD,
ReadingDate,
Reading
FROM #temp
)
SELECT MeteriD,SUM(Reading) Reading ,MIN(ReadingDate) ReadingDate from CTE Group by Number,MeterID

Interesting use of DENSE_RANK(). It's not too bad considering there are two sorts in the plan, but this, with only one sort, is faster:

SELECT MeteriD,

Reading = SUM(Reading),

x.DateByHour

FROM #temp

CROSS APPLY (SELECT DateByHour = DATEADD(hour,DATEDIFF(hour,0, ReadingDate),0)) x

GROUP BY MeterID, x.DateByHour

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I wouldn't use the cross apply, but it's just a personal preference.

It should be interpreted the same way as Chris M's query.

SELECT MeteriD,

Reading = SUM(Reading),

DateByHour = DATEADD(hour,DATEDIFF(hour,0, ReadingDate),0)

FROM #temp

GROUP BY MeterID, DATEADD(hour,DATEDIFF(hour,0, ReadingDate),0)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Luis Cazares (5/13/2014)
I wouldn't use the cross apply, but it's just a personal preference.
It should be interpreted the same way as Chris M's query.
SELECT MeteriD,
Reading = SUM(Reading),
DateByHour = DATEADD(hour,DATEDIFF(hour,0, ReadingDate),0)
FROM #temp
GROUP BY MeterID, DATEADD(hour,DATEDIFF(hour,0, ReadingDate),0)

The plans are identical.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

smart_analyst Ten Centuries Points: 1037 More actions · Answer 6

smart_analyst

Ten Centuries

Points: 1037

May 13, 2014 at 6:18 pm

#1713287

Thank you everyone it worked.

smart_analyst Ten Centuries Points: 1037 More actions · Answer 7

I want to aggregate to monthly values for the reading. Currently it is displaying. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and similar to previous one but, still tried to follow the etiquette.

MeterIDReadingdateReading

3969 22/10/2013 0:150

3969 22/10/2013 0:300

3969 22/10/2013 0:450

3969 22/10/2013 1:000

3969 22/10/2013 1:150

3969 22/10/2013 1:300

3969 22/10/2013 1:450

3969 22/10/2013 2:001

3969 22/10/2013 2:150

MeterId int

ReadingDate datetime

Reading real

-===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

meterID INT PRIMARY KEY,

Readingdate DATETIME,

reading real

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL

SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL

SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL

SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL

SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL

SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL

SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL

SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL