score:3
Accepted answer
Using below sample data:
select * into #t from (
select 1 ID, '2016-04-06 09:00:00' r_start , '2016-04-06 09:15:00' r_end union
select 2, '2016-04-06 11:00:00', '2016-04-06 11:30:00'union
select 3, '2016-04-06 12:00:00', '2016-04-06 12:45:00'union
select 4, '2016-04-06 16:30:00', '2016-04-06 16:45:00') AS bookdata
following query, results all the free time slots:
;with booked as (
select r_start, r_end
, LAG(r_end) over (order by r_end) PrevBook
, LEAD(r_start) over (order by r_start) NextBook
from #t
)
select IsNull(PrevBook, '2016-04-06 07:00:00') AS FreeStart, r_start AS FreeEnd
from booked
union
select r_end, IsNull(NextBook, '2016-04-06 19:00:00')
from booked
Result
+---------------------+---------------------+
| FreeStart | FreeEnd |
+---------------------+---------------------+
| 2016-04-06 07:00:00 | 2016-04-06 09:00:00 |
| 2016-04-06 09:15:00 | 2016-04-06 11:00:00 |
| 2016-04-06 11:30:00 | 2016-04-06 12:00:00 |
| 2016-04-06 12:45:00 | 2016-04-06 16:30:00 |
| 2016-04-06 16:45:00 | 2016-04-06 19:00:00 |
+---------------------+---------------------+
If you are using SQL older than 2012 you won't have LEAD and LAG so you can use below query instead, assuming your have ID as primary key
;with booked as (
select r_start, r_end
, (select top 1 r_end from #t where ID < tbl.ID order by ID desc) PrevBook
, (select top 1 r_start from #t where ID > tbl.ID order by ID) NextBook
from #t tbl
)
select IsNull(PrevBook, '2016-04-06 07:00:00') AS FreeStart, r_start AS FreeEnd
from booked
union
select r_end, IsNull(NextBook, '2016-04-06 19:00:00')
from booked
** Please "Mark as Answer" if this post has answered the question
score:1
this works for the data you provided
select * into #t from (
select '2016-04-06 09:00:00'r_start , '2016-04-06 09:15:00'r_end union all
select'2016-04-06 11:00:00', '2016-04-06 11:30:00'union all
select'2016-04-06 12:00:00', '2016-04-06 12:45:00'union all
select'2016-04-06 16:30:00', '2016-04-06 16:45:00') x
--returns duration between 1st reservation and the next
select
datediff(MINUTE,x.r_end,y.r_start)/60 'hours'
,datediff(MINUTE,x.r_end,y.r_start) - datediff(MINUTE,x.r_end,y.r_start)/60 * 60 'minutes'
,x.r_start
,x.r_end
from
(select r_start ,r_end
,row_number() over (order by r_start asc) rowid
from #t) x
left join (select r_start ,r_end
,row_number() over (order by r_start asc) rowid
from #t) y
on x.rowid = y.rowid - 1
--returns unreserved dates
select
x.r_end available_from
,y.r_start available_to
from
(select r_start ,r_end
,row_number() over (order by r_start asc) rowid
from #t) x
left join (select r_start ,r_end
,row_number() over (order by r_start asc) rowid
from #t) y
on x.rowid = y.rowid - 1
More questions
- Calculate free time Slots from reservation table
- Get the SUM of TIME datatypes (MSSQL08) from a table
- SQL query to calculate part of visit duration from log table
- How can i get the first free ID from table
- Calculate arithmetic return from a table of values
- How can I determine down time from logs in database table
- Copy data from a SQL Server table into a historic table and add a timestamp of the time copied?
- T-SQL / SQL Server : help to calculate turnover time for account to become positve from negative balance
- Inserting one record at a time to multiple tables from a table
- calculate daily average sale from sql table
- Calculate time from 1 column
- How do I create a table the same time as other tables that pulls totals from each?
- Taking long time to fill data table from data adapter C#
- Update only time in a SQL Server DateTime column from another DateTime column in the same table
- How to check the data and time that a table was created in IBM Netezza SQL from Aginity workbench
- find active records from table based on date time in php and sql server
- SQL Server: query to get the data between two values from same columns and calculate time difference
- select first in and last out time from door access table
- SQL Server 2012: Calculating total time elapsed for every unique record from a logs table
- Calculate total running time from start/stop timestamps
- how to calculate time duration within a table
- Set time in DateTime from another Column in same table
- sql query to calculate the stock of each product from table of sells and table the purchases
- How to calculate total experience from ExperienceHistory table in sql server?
- Calculate the 2 columns from table and insert the result as other column in the same table
- Calculate time duration between differents records in a table based on datetimes
- Query to get unique status from tracking table for a given time
- Calculate average time from DateTime2 column
- Inserting into the same db table from two different programs at the same time
- How to avoid GETDATE() from updating every time table is accessed [SQL Server 2014]
More questions with similar tag
- SQL SERVER - Set variable in WHERE clause
- Is there an UPDATE equivalent command to SELECT json data
- SQL Server 2005 Isolation level changes on service restart, how to stop it?
- Select random rows and stop when a specific sum/total is reached
- Create two columns from one, based on a different where clause
- SQL Server hash algorithms
- SQL Query to XML with tag attributes
- Count column with specific condition
- What is the equivalent SQL Server data type for Access attachment?
- Struggling to add column and join in SQL Server
- SQL Server 2016 JSON: Select array of strings instead of array of objects
- How and where to handle exceptions in a 3-tier web application? Specifically Sql Database Exceptions
- Function to replace words in a inputstring based on values in a table
- How to find PolicyNumber that have more than one DIFFERENT SICCode from the same source table
- Trunc(sysdate) in SQL Server
- I want to select specific values from a SQL Server table
- How to determine if its a Referential Integrity Violation exception
- How to run multiple Alter stored procedure scripts as a single one ?
- ColdFusion ORM Mapping Multiple One-To-One Foreign Key Properties to The Same PK in Another Table
- Why my sql query is so slow in one database?
- SELECT DISTINCT and ORDER BY
- Get the information in terms of time about people who are free on a particular day
- SQL Server xml string parsing in varchar field
- Remove or adding additional string to a query
- SQL Server - EBS Storage Design
- Why I can not insert or update valid data after Instead of Trigger has been fired?
- Dynamics AX 2012: Conversion failed when converting date and/or time from character string
- SQL Server get latest value by date
- Expects parameter '@CustomerPhoto', which was not supplied
- Why is this SQL Server query deadlocking?
Source:
stackoverflow.com