I have a table of appointments with records having two fields - start_date and end_date, both datetime. There is no overlap of time periods in the table.
Given a specific period (search_start and search_end), I need to generate a list of all openings between those appointments (from and to) using SQL.
For example: given two appointments in the table:
September 15, 2016 08:00 to September 15, 2016 09:00
September 15, 2016 10:00 to September 15, 2016 12:00
And given search parameters start= September 1, 2016 00:00 and end= September 30, 2016 23:59, the results should be
September 1, 2016 00:00 to September 15, 2016 08:00
September 15, 2016 09:00 to September 15, 2016 10:00
September 15, 2016 12:00 to September 30, 2016 23:59
Here is a script to generate a sample table:
CREATE TABLE [dbo].[Table_1]( [from_date] [datetime] NOT NULL, [to_date] [datetime] NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [from_date] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT [dbo].[Table_1] ([from_date], [to_date]) VALUES (CAST(0x0000A6820083D600 AS DateTime), CAST(0x0000A682009450C0 AS DateTime))
INSERT [dbo].[Table_1] ([from_date], [to_date]) VALUES (CAST(0x0000A68200A4CB80 AS DateTime), CAST(0x0000A68200C5C100 AS DateTime))
I am using MSSQL 2008 R2
Using your values I got the output you wanted : )
DECLARE @start datetime = '2016-09-01 00:00:00' DECLARE @finish datetime = '2016-09-30 23:59:00' WITH rn AS (SELECT *, ROW_NUMBER() OVER (ORDER BY start) AS rn FROM opening) SELECT CASE WHEN r1.rn = 1 THEN @start ELSE r2.finish END as START, CASE WHEN r1.rn IS NULL THEN @finish ELSE r1.start END AS FINISH FROM rn r1 FULL OUTER JOIN rn r2 ON r1.rn = r2.rn + 1 WHERE ISNULL(r1.start, 0) != @start
opening is your schedule/appointment table.
start is the starting date in your table and
finish is the end date in your table.
@start is the starting date and
@finish is the end date. You obvious don't need to use
@start, @finish. I just put it there for testing.