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)

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;

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

quote:

---

Originally posted by Ciupaz

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

Luigi

---

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

---