Aggregate 15min's values 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 2012 Forums

Transact-SQL (2012)

Aggregate 15min's values in hours

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-15 : 09:19:58

Hello all,
having a table with values associated with time values (for every 15 minutes), like this:

Value - DateRec
10 - '2013-01-01 23:00:00'
10 - '2013-01-01 23:15:00'
16 - '2013-01-01 23:30:00'
17 - '2013-01-01 23:45:00'
12 - '2013-01-02 00:00:00'
5 - '2013-01-02 00:15:00'
7 - '2013-01-02 00:30:00'
2 - '2013-01-02 00:45:00'
13 - '2013-01-02 01:00:00'
1 - '2013-01-02 01:15:00'
1 - '2013-01-02 01:30:00'
20 - '2013-01-02 01:45:00'
30 - '2013-01-02 02:00:00'
10 - '2013-01-02 02:15:00'
10 - '2013-01-02 02:30:00'

I have to create a query with a DateTime parameter hour based, like:

'2013-01-02 01:00:00'

and the SUM of the values "inside" this hour (starting from 15min).
In my example these records:

1 - '2013-01-02 01:15:00'
1 - '2013-01-02 01:30:00'
20 - '2013-01-02 01:45:00'
30 - '2013-01-02 02:00:00'

So obtain 1+1+20+30 = 52.

How can I write this query?

Thanks a lot.

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 09:58:43

This?

DECLARE @param DATETIME = '2013-01-02 01:00:00';

SELECT @param AS [DateRec],SUM([value]) AS TotalForHour FROM theTable
WHERE DateRec > @param AND DateRec <= DATEADD(hh,1,@param)

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-15 : 10:26:38

Perfect, thank you James.

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 11:27:42

Sure. You are welcome.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-16 : 09:54:01

Hi James,
a similar question.
Now I have a quarter of an hour table, with numbers for every quarter of a day,
and some values fields, like this way:

QuartHour - DateRef - Value1 - Value2 - Value3
1 - 2012-12-31 23:15:00 - 10 - 20 - 30
2 - 2012-12-31 23:30:00 - 10 - 21 - 30
3 - 2012-12-31 23:45:00 - 10 - 22 - 30
4 - 2013-01-01 00:00:00 - 16 - 23 - 30
5 - 2013-01-01 00:15:00 - 15 - 24 - 30
6 - 2013-01-01 00:30:00 - 14 - 20 - 30
7 - 2013-01-01 00:45:00 - 13 - 21 - 30
8 - 2013-01-01 01:00:00 - 13 - 20 - 30
...
96 - 2013-01-01 23:00:00 - 19 - 26 - 30

With these values I have to populate an "Hours" table, like this:

Hour - DateRef - Computed1 - Computed2 - Computed3
1 - 2013-01-01 00:00:00 - 11.5 - 21.5 - 30
2 - 2013-01-01 01:00:00 - 13.75 - 21.25 - 30
....
24 - 2013-01-02 00:00:00 -...

where in the DataRef field I have to put the end of the hour (starting from 15min behind)
and in the Computed field, the average of the 4 field in that hour.

In my example, for the first hour, I have to put 2013-01-01 00:00:00 (the end)
and
11.5 -> (10+10+10+16) / 4
21.5 -> (20+21+22+23) / 4
30 -> (30+30+30+30) / 4

Could you please help me?

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 11:45:28

I could have sworn I posted a reply to you, but obviously it didn't show up. I will find it or rewrite it and post it again.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 11:58:49

[code]CREATE TABLE #data(Hour INT, DateRef DATETIME, Value1 INT, Value2 INT, value3 INT);
insert into #data values ('1','2012-12-31 23:15:00','10','20','30');
insert into #data values ('2','2012-12-31 23:30:00','10','21','30');
insert into #data values ('3','2012-12-31 23:45:00','10','22','30');
insert into #data values ('4','2013-01-01 00:00:00','16','23','30');
insert into #data values ('5','2013-01-01 00:15:00','15','24','30');
insert into #data values ('6','2013-01-01 00:30:00','14','20','30');
insert into #data values ('7','2013-01-01 00:45:00','13','21','30');
insert into #data values ('8','2013-01-01 01:00:00','13','20','30');

SELECT
DATEPART(hour,DATEADD(hh,(DATEDIFF(mi,0,DateRef )+45)/60,0))+1 AS Hour,
DATEADD(hh,(DATEDIFF(mi,0,DateRef )+45)/60,0) AS DateRef,
AVG(Value1*1E) AS Computed1,
AVG(Value2*1E) AS Computed2,
AVG(Value3*1E) AS Computed3
FROM
#data
GROUP BY
DATEADD(hh,(DATEDIFF(mi,0,DateRef )+45)/60,0);

DROP TABLE #data;[/code]

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-16 : 14:28:55

Perfect (just a little correction on table field name. Hour -> Quart).
You're great James!.

Luigi

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-17 : 10:33:37

James, why put the 1E in the expression of AVG?

Luigi

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-17 : 10:47:05

Another little adding.
In my quart hour table I have a field that has 1 or 0 values.
When I aggregate for the hour, I have to get 0 if at least of one of the 4 four values is 0, instead 1.

For example, for these records:

QuartHour - DateRef - Value1 - Value2 - Value3 - Flag
5 - 2013-01-01 00:15:00 - 15 - 24 - 30 - 1
6 - 2013-01-01 00:30:00 - 14 - 20 - 30 - 0
7 - 2013-01-01 00:45:00 - 13 - 21 - 30 - 1
8 - 2013-01-01 01:00:00 - 13 - 20 - 30 - 1

I have to get 0 in my SELECT clause (because there is at least one zero in these four fields).

I've tried to write:

SELECT
...
CASE WHEN Flag = 0 THEN 0 ELSE 1
...

but is does not compile correctly.

Can you help me?

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 11:22:32

If the Flag is always 1 or 0, can you do the following?

	MIN(Flag)*AVG(Value1*1E) AS Computed1,
	MIN(Flag)*AVG(Value2*1E) AS Computed2,
	MIN(Flag)*AVG(Value3*1E) AS Computed3

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 11:23:59

quote:
Originally posted by Ciupaz

James, why put the 1E in the expression of AVG?

Luigi

That is just so I can force the values to floating point type. Otherwise it will do integer calculations - you wouldn't get 11.5, the result would be 11 with the fractional part chopped off.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-17 : 14:12:35

The flag is a "bit" values, so could be True or False.

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 17:55:15

If it is of data type BIT, then doing what I had posted earlier should work.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-18 : 05:01:16

Another problem James.
When I make the SELECT statement, I have to calculate a field (field3) in this way:

Field3 = (Field2 - Field2 of the preceeding hour) / 60.

I'd prefer not use cursors, but how can I write a simil calculations?

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 05:12:35

quote:
Originally posted by Ciupaz

Another problem James.
When I make the SELECT statement, I have to calculate a field (field3) in this way:

Field3 = (Field2 - Field2 of the preceeding hour) / 60.

I'd prefer not use cursors, but how can I write a simil calculations?

Luigi

see scenario 2 here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources