Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Please
log in
to post a comment.
math function
OFFSET FETCH Clause
Empleados
Cross Apply vs Inner Join
Combined Where and having
Where clause inside sub query
estructura
Escola
using lead() and sum() with over() in mssql
GKM_JOIN
Please log in to post a comment.