user1480192 user1480192 - 2 months ago 6
SQL Question

Find available openings using SQL

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]

GO

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

Answer

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.