/*
DECLARE @tab TABLE (nn INT, d1 INT)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 1, 30)  -- получасы (1) и минуты (30)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 2, 60)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 3, 90)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 4, 120)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 5, 150)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 6, 180)
INSERT INTO @tab ( nn, d1 ) VALUES  ( 7, 210)
*/

--SELECT dateadd(day,datediff(day,(0),GETDATE()),(0))

DECLARE @tab_date TABLE (dd smalldatetime)
INSERT INTO @tab_date (dd ) VALUES  ('20120911 00:24:12.120')
INSERT INTO @tab_date (dd ) VALUES  ('20120911 01:45:12.120')
INSERT INTO @tab_date (dd ) VALUES  ('20120911 18:45:12.089')
INSERT INTO @tab_date (dd ) VALUES  ('20120911 23:45:12.089')

SELECT 
dd,
1+ DATEDIFF(MI, dateadd(day,datediff(day,(0),GETDATE()),(0)), dd) AS minut,  
1 + (DATEDIFF(MI, dateadd(day,datediff(day,(0),GETDATE()),(0)), dd)/30) AS half_hour
FROM @tab_date

declare @table1 table (
	[device_id]	int							NULL,
	[somedate]	datetime				NULL,
	[value]			decimal(38,19)	NULL
);

declare
	@i					int,
	@startdate	datetime,
	@somedate		datetime,
	@somevalue	decimal(38,19)
;

set @startdate = '2012-09-11 00:00:00.000';
set @i = 0;
while @i <= 47
begin
	set @somedate = dateadd(minute,30*@i,@startdate);
	set @somevalue = rand();
	insert into @table1 (device_id, somedate, value)
	values (1, @somedate, @somevalue);
	set @i = @i + 1;
end;

select * from @table1;

declare @table1 table (
	[device_id]	int							NULL,
	[somedate]	datetime				NULL,
	[value]			decimal(38,19)	NULL
);

declare
	@i						int,
	@startdate		datetime,
	@enddate			datetime,
	@currentdate	datetime,
	@somevalue		decimal(38,19),
	@increment		int
;

set @startdate = '2012-09-11 00:00:00.000';
set @enddate = dateadd(day,1,@startdate);
set @currentdate = @startdate;
while @currentdate <= @enddate
begin
	set @somevalue = rand();
	insert into @table1 (device_id, somedate, value)
	values (1, @currentdate, @somevalue);
	set @increment = cast(rand()*300 as int);
	set @currentdate = dateadd(second,@increment,@currentdate);
end;

select * from @table1;

select
	sq.datehour,
	sum(sq.value) as hour_sum
from (
select
	t.value,
	datepart(hour,t.somedate) as datehour
from @table1 as t) as sq
group by sq.datehour;

declare @table1 table (
	[device_id]	int							NULL,
	[somedate]	datetime				NULL,
	[value]			decimal(38,19)	NULL
);

declare
	@i						int,
	@startdate		datetime,
	@enddate			datetime,
	@currentdate	datetime,
	@somevalue		decimal(38,19),
	@increment		int
;

set @startdate = '2012-09-11 00:00:00.000';
set @enddate = dateadd(day,1,@startdate);
set @currentdate = @startdate;
while @currentdate <= @enddate
begin
	set @somevalue = rand();
	insert into @table1 (device_id, somedate, value)
	values (1, @currentdate, @somevalue);
	set @increment = cast(rand()*300 as int);
	set @currentdate = dateadd(second,@increment,@currentdate);
end;

select * from @table1;

select
	sq.datehour,
	sum(sq.value) as hour_sum
from (
select
	t.value,
	datepart(hour,t.somedate) as datehour
from @table1 as t) as sq
group by sq.datehour;

with Device as 
(
  select *
    from (values(1), (2), (3)) Q(deviceID)
), DT as
(
  select n = 1, dt = getdate()
  union all
  select n = n + 1, dt = dateadd(minute, 30, dt)
    from DT
   where n < 48
), data as
(
  select A.deviceID, B.dt, value = A.deviceID + B.n
    from Device A
   cross join DT B
)
select deviceID,
       dt = dateadd(hour, datediff(hour, '', dt), ''),
       value = sum(value)
  from data
 group by deviceID, dateadd(hour, datediff(hour, '', dt), '')
 order by 1, 2

SELECT	 D.DeviceID
	,X.[Hour]
	,Sum(D.Value)	AS Value
FROM	dbo.Data D CROSS APPLY (SELECT
		 DateAdd(Hour,DateDiff(Hour,0,D.DT),0)
	) X ([Hour])
GROUP BY DeviceID
	,X.[Hour]