Availible schedules, Sql Server

Availible schedules

if object_id('tempdb..#r') is not null
		drop table #r
	create table #r ( ReservationID int ,   ReservationDateFrom datetime   ,     ReservationDateTo  datetime,    ReservationRoom  int)

--insert into #r values(1,         '2017-01-02 00:00:00.000'    ,'2017-01-02 02:00:00.000' ,       14   )           
	insert into #r values(2,         '2017-01-02 04:00:00.000'    ,'2017-01-02 05:00:00.000' ,        14  )            
	insert into #r values(3,         '2017-01-02 06:00:00.000'    ,'2017-01-02 08:00:00.000' ,        14  )            
	insert into #r values(4,         '2017-01-02 08:30:00.000'    ,'2017-01-02 09:30:00.000' ,        14  )            
	insert into #r values(5,         '2017-01-02 09:50:00.000'    ,'2017-01-02 11:00:00.000' ,        14  )
	insert into #r values(6,         '2017-01-02 13:00:00.000'    ,'2017-01-02 15:00:00.000' ,        14  )

declare @dt datetime ='2017/01/02', @room int = 14

; with data as
	(
		select ReservationDateFrom, ReservationDateTo,ReservationRoom
		from #r
		where ReservationRoom = @room
			and ReservationDateFrom between @dt and @dt+1
		union 
		select @dt,@dt,@room
		union
		select @dt+1,@dt+1,@room
	)
	,mid as
	(
		select *,
			(select top 1 ReservationDateFrom 
			from data 
			where ReservationRoom =a.ReservationRoom 
				and ReservationDateFrom> a.ReservationDateTo 
			order by ReservationDateFrom) [next]
		from data a			
	 )
	 select
		cast(Reservationdateto as time) TimeIn,
		cast(next as time) [TimeOut], 
		datediff(mi,ReservationDateTo, next) [Minutes]
	 from mid
	 where datediff(mi,ReservationDateTo, next)>0


run \| edit \| history \| help	0