Remove overlapping date intervals-CodePudding

I have two tables in my sql server database :

PlannedPeriods : containing periods of future events
UnavailabledPeriods : list or periods that are not available

Here's an example for structure and data :

CREATE TABLE PlannedPeriods(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateFrom] [datetime] NOT NULL,
    [DateTo] [datetime] NOT NULL,
    [Event] [nvarchar](max) NULL
)
INSERT INTO PlannedPeriods (DateFrom, DateTo, Event)
VALUES
    ('2022-08-29 12:00:00', '2022-08-30 04:00:00', 'Event 1'),
    ('2022-08-30 12:00:00', '2022-08-30 14:00:00', 'Event 2'),
    ('2022-08-31 18:30:00', '2022-08-31 22:30:00', 'Event 3')


CREATE TABLE UnavailabledPeriods(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateFrom] [datetime] NOT NULL,
    [DateTo] [datetime] NOT NULL
)
INSERT INTO UnavailabledPeriods (DateFrom, DateTo)
VALUES
    ('2022-08-30 01:00:00', '2022-08-30 03:00:00'),
    ('2022-08-31 15:00:00', '2022-08-31 19:00:00')

I would like to remove all unavailable periods from the planned periods in order to obtain this result :

DateFrom             | DateTo
2022-08-29 12:00:00  | 2022-08-30 01:00:00
2022-08-30 03:00:00  | 2022-08-30 04:00:00 
2022-08-30 12:00:00  | 2022-08-30 14:00:00
2022-08-31 19:00:00  | 2022-08-31 22:30:00

CodePudding user response：

In this case you should be able to collect all dates into a new table and work on it.

DECLARE @Results AS TABLE(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateValue] [datetime] NOT NULL,
    [DateCode] varchar(10),
    Event varchar(10)
)

INSERT INTO @Results 
SELECT DateFrom, 'PlannedStart', Event FROM PlannedPeriods
UNION ALL
SELECT DateTo, 'PlannedEnd', Event FROM PlannedPeriods
UNION ALL
SELECT DateFrom, 'UnavailableStart', NULL FROM UnavailabledPeriods
UNION ALL
SELECT DateTo, 'UnavailableEnd', NULL FROM UnavailabledPeriods
ORDER BY 1

SELECT A.DateValue AS DateFrom, B.DateValue AS DateTo
, ISNULL(A.Event, B.Event) AS Event
FROM @Results AS A
INNER JOIN @Results AS B ON A.Id = B.Id - 1
Where A.DateCode IN ('PlannedStart', 'UnavailableEnd')
AND B.DateCode NOT IN ('UnavailableEnd')