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 2012 Forums
 Transact-SQL (2012)
 Aggregate 15min's values in hours
 New Topic  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

3643 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

3643 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

3643 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

3643 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

3643 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

3643 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

3643 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
52317 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  
 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.09 seconds. Powered By: Snitz Forums 2000