SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE BookingSession
(
    id_BookingSession INT
    ,SessionStart DATETIME
    ,SessionEnd DATETIME
);

INSERT INTO BookingSession (id_BookingSession,SessionStart,SessionEnd)
VALUES
(
    1,'20260507 09:00','20260507 17:00'
);

CREATE TABLE Bookings
(
    id_BookingSession INT
    ,id_BookingSession_Slot INT
    ,TimeFrom DATETIME
    ,TimeUntil DATETIME
);

--Make some bookings:
INSERT INTO Bookings (id_BookingSession,id_BookingSession_Slot,TimeFrom,TimeUntil) 
VALUES
(1,90,'20260507 10:05','20260507 10:45')  -- 40 Min booking
,(1,91,'20260507 11:05','20260507 11:30') -- 25 Min booking
,(1,93,'20260507 11:30','20260507 12:30') -- 60 Min booking (So no gap before last one);
4 rows affected

SELECT * FROM BookingSession;
SELECT * FROM Bookings; 


id_BookingSession
SessionStart
SessionEnd


1
2026-05-07 09:00:00.000
2026-05-07 17:00:00.000




id_BookingSession
id_BookingSession_Slot
TimeFrom
TimeUntil


1
90
2026-05-07 10:05:00.000
2026-05-07 10:45:00.000

1
91
2026-05-07 11:05:00.000
2026-05-07 11:30:00.000

1
93
2026-05-07 11:30:00.000
2026-05-07 12:30:00.000


SELECT
  id_BookingSession
 ,TimeFrom
 ,TimeUntil
FROM
  Bookings

UNION ALL

SELECT
  id_BookingSession
 ,TimeFrom
 ,TimeUntil
FROM
  BookingSession
CROSS APPLY
(
  SELECT
    NULL,
    SessionStart

  UNION ALL

  SELECT
    SessionEnd,
    NULL
)
  AS FencePosts (
       TimeFrom
      ,TimeUntil
  )

ORDER BY
  1,2,3


id_BookingSession
TimeFrom
TimeUntil


1
null
2026-05-07 09:00:00.000

1
2026-05-07 10:05:00.000
2026-05-07 10:45:00.000

1
2026-05-07 11:05:00.000
2026-05-07 11:30:00.000

1
2026-05-07 11:30:00.000
2026-05-07 12:30:00.000

1
2026-05-07 17:00:00.000
null


WITH
  combined AS
(
  SELECT
    id_BookingSession
   ,TimeFrom
   ,TimeUntil
  FROM
    Bookings

  UNION ALL

  SELECT
    id_BookingSession
   ,NULL
   ,SessionStart
  FROM
    BookingSession


  UNION ALL

  SELECT
    id_BookingSession
   ,SessionEnd
   ,NULL
  FROM
    BookingSession
),
  gaps AS
(
  SELECT
    id_BookingSession,
    TimeUntil AS TimeFrom,
    LEAD(TimeFrom) OVER (
      PARTITION BY id_BookingSession
          ORDER BY TimeFrom
      )
        AS TimeUntil
  FROM
    combined
)
SELECT
  *
FROM
  gaps
WHERE
  TimeFrom < TimeUntil
ORDER BY
  1,2,3


id_BookingSession
TimeFrom
TimeUntil


1
2026-05-07 09:00:00.000
2026-05-07 10:05:00.000

1
2026-05-07 10:45:00.000
2026-05-07 11:05:00.000

1
2026-05-07 12:30:00.000
2026-05-07 17:00:00.000

id_BookingSession	id_BookingSession_Slot	TimeFrom	TimeUntil
1	90	2026-05-07 10:05:00.000	2026-05-07 10:45:00.000
1	91	2026-05-07 11:05:00.000	2026-05-07 11:30:00.000
1	93	2026-05-07 11:30:00.000	2026-05-07 12:30:00.000

id_BookingSession	TimeFrom	TimeUntil
1	null	2026-05-07 09:00:00.000
1	2026-05-07 10:05:00.000	2026-05-07 10:45:00.000
1	2026-05-07 11:05:00.000	2026-05-07 11:30:00.000
1	2026-05-07 11:30:00.000	2026-05-07 12:30:00.000
1	2026-05-07 17:00:00.000	null