Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Aggregate 15min's values in hours
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/15/2013 :  09:19:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/15/2013 :  09:58:43  Show Profile  Reply with Quote
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)
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/15/2013 :  10:26:38  Show Profile  Reply with Quote
Perfect, thank you James.

Luigi
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/15/2013 :  11:27:42  Show Profile  Reply with Quote
Sure. You are welcome.
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/16/2013 :  09:54:01  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/16/2013 :  11:45:28  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/16/2013 :  11:58:49  Show Profile  Reply with Quote
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;
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/16/2013 :  14:28:55  Show Profile  Reply with Quote
Perfect (just a little correction on table field name. Hour -> Quart).
You're great James!.

Luigi
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/17/2013 :  10:33:37  Show Profile  Reply with Quote
James, why put the 1E in the expression of AVG?

Luigi
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/17/2013 :  10:47:05  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/17/2013 :  11:22:32  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/17/2013 :  11:23:59  Show Profile  Reply with Quote
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.
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/17/2013 :  14:12:35  Show Profile  Reply with Quote
The flag is a "bit" values, so could be True or False.

Luigi
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/17/2013 :  17:55:15  Show Profile  Reply with Quote
If it is of data type BIT, then doing what I had posted earlier should work.
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 01/18/2013 :  05:01:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/18/2013 :  05:12:35  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next 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.11 seconds. Powered By: Snitz Forums 2000