declare @sb table(device_id int,start_date datetime,end_date datetime,sboy_id int,finished int);
set dateformat dmy;
insert into @sb(device_id,start_date,end_date,sboy_id,finished) 
	Select 11270,	'09.01.2017 8:35',		'09.01.2017 19:35',	101,	1 union
	Select 11270,	'09.01.2017 19:35',	'10.01.2017 8:35',		100,	1 union
	Select 11272,	'10.01.2017 8:35',	'10.01.2017 19:35',		1,		1 union
	Select 11272,	'11.01.2017 8:35',	'11.01.2017 19:35',		23,		1 union
	Select 11272,	'11.01.2017 19:35',	'12.01.2017 8:35',		58,		1 union
	Select 11270,	'12.01.2017 8:35',	'12.01.2017 10:05',		101,	1 union
	Select 11270,	'12.01.2017 10:05',	'12.01.2017 10:15',		34,		1 union
	Select 11270,	'12.01.2017 10:15',	'12.01.2017 19:35',		28,		1

--Select * from @sb

;with 
borderdates(maxdate,startdate) as (
	Select max(end_date), dateadd(hour, datepart(hour, min(start_date)), Cast(Floor(Cast(min(start_date) as Float)) as Datetime)) from @sb
),
schedule(datestart, datefinish) as (
	Select borderdates.startdate, dateadd(hour, 1, borderdates.startdate) from borderdates
	union all
	Select schedule.datefinish, dateadd(hour, 1, schedule.datefinish) from schedule
	cross join borderdates
	where schedule.datefinish <= borderdates.maxdate

)
Select 
Cast(Floor(Cast(s.datestart as float)) as datetime) as [date],
Right('0' + convert(varchar,datepart(hour, s.datestart)) + ':00',5) as [time],
b.*
from schedule s
inner join @sb b on s.datestart between b.start_date and b.end_date
order by s.datestart asc

declare @sb table(device_id int,start_date datetime,end_date datetime,sboy_id int,finished int);
set dateformat dmy;
insert into @sb(device_id,start_date,end_date,sboy_id,finished) 
	Select 11270,	'09.01.2017 8:35',		'09.01.2017 19:35',	101,	1 union
	Select 11270,	'09.01.2017 19:35',	'10.01.2017 8:35',		100,	1 union
	Select 11272,	'10.01.2017 8:35',	'10.01.2017 19:35',		1,		1 union
	Select 11272,	'11.01.2017 8:35',	'11.01.2017 19:35',		23,		1 union
	Select 11272,	'11.01.2017 19:35',	'12.01.2017 8:35',		58,		1 union
	Select 11270,	'12.01.2017 8:35',	'12.01.2017 10:05',		101,	1 union
	Select 11270,	'12.01.2017 10:05',	'12.01.2017 10:15',		34,		1 union
	Select 11270,	'12.01.2017 10:15',	'12.01.2017 19:35',		28,		1
;with 
borderdates(maxdate,startdate) as (
	Select max(end_date), dateadd(hour, datepart(hour, min(start_date)), Cast(Floor(Cast(min(start_date) as Float)) as Datetime)) from @sb
),
schedule(datestart, datefinish) as (
	Select borderdates.startdate, dateadd(hour, 1, borderdates.startdate) from borderdates
	union all
	Select dateadd(hour, 1, schedule.datestart), dateadd(hour, 2, schedule.datestart) from schedule
	cross join borderdates
	where schedule.datefinish <= borderdates.maxdate

)
Select 
Cast(Floor(Cast(s.datestart as float)) as datetime) as [date],
Right('0' + convert(varchar,datepart(hour, s.datestart)) + ':00',5) as [time],
s.datestart,
s.datefinish,
datediff(minute, 
	case when b.start_date between s.datestart and s.datefinish then b.start_date else s.datestart end,
	case when b.end_date between s.datestart and s.datefinish then b.end_date else s.datefinish end
) as [duration,min]
b.*
from schedule s
inner join @sb b on not (s.datestart > b.end_date or s.datefinish < b.start_date)
order by sboy_id, s.datestart

option (maxrecursion 32767)

with tnum as (
	select ROW_NUMBER() OVER (order by object_id) as num from sys.all_columns
) select DATEADD(HH,num-1,convert(datetime,'20170101')) as datehour from tnum

set nocount on;
use tempdb;
go
declare @t table(
  dv int not null check(dv>0),
  bg datetime not null,
  fn datetime null,
  fid int not null,
  rcv bit not null,
  check(bg<=isnull(fn,getdate()))
);
insert @t(dv,bg,fn,fid,rcv)
values(11270,'20170109 08:35','20170109 19:35',101,1),
      (11270,'20170109 19:35','20170110 08:35',100,1),
      (11272,'20170110 08:35','20170110 19:35',1,1),
      (11272,'20170110 19:23','20170110 19:35',-10,1),
      (11272,'20170110 19:35','20170111 08:35',-10,1),
      (11272,'20170111 08:35','20170111 19:35',23,1),
      (11272,'20170111 19:35','20170112 08:35',58,1),
      (11270,'20170112 08:35','20170112 10:05',103,1),
      (11270,'20170112 10:05','20170112 10:15',34,1),
      (11270,'20170112 10:15','20170112 19:35',28,1),
      (42170,'20170323 23:59','20170324 00:05',170,1),
      (11270,'20170803 19:15',null,5,0);
with
  b as(select dv,bg,fid,isnull(fn,getdate()) fn,
       dateadd(hh,datediff(hh,0,bg)+1,0) hh from @t
       union all
       select dv,bg,fid,fn,dateadd(hh,1,hh) from b where hh<fn),
  c as(select *,
         iif(datediff(mi,bg,fn)<60,datediff(mi,bg,fn),
             iif(fn<hh,datepart(mi,fn),
                 iif(datediff(mi,bg,hh)<60,datediff(mi,bg,hh),60))) mi
       from b)
select dv,fid,dateadd(hh,-1,hh) hh,sum(mi) mi from c
--where fid in(100,101,1,170,34,-10)
group by dv,bg,hh,fid
order by dv,bg,hh
option(maxrecursion 0);