As long as I’m documenting interesting puzzle solutions, I figured I might as well post this one. I was asked to display a calendar/schedule/agenda in 15 minute increments between two time periods using SQL as a back end. This solution uses a base table to define the start/end time, and the magic of SQL CVE to expand that to a years worth of 15 minute time blocks that could be used to join against a table which stores the taken slots.
CREATE TABLE [dbo].[OpenClosePeriod](
[OpenTime] [datetime] NOT NULL,
[CloseTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO OpenClosePeriod (OpenTime, CloseTime)
VALUES('2022-01-01 09:00:00.000', '2022-01-01 20:00:00.000')
GO
WITH D00(N) AS (SELECT 1 UNION ALL SELECT 15)
,D02(N) AS (SELECT 1 FROM D00 a, D00 b)
,D04(N) AS (SELECT 1 FROM D02 a, D02 b)
,D08(N) AS (SELECT 1 FROM D04 a, D04 b)
,D16(N) AS (SELECT 1 FROM D08 a, D08 b)
,cteTally(N,Q) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 15 FROM D16)
,DateRange AS (
SELECT ExplodedDate = DATEADD(DAY,N - 1,(SELECT TOP 1 OpenTime FROM OpenClosePeriod WHERE YEAR(OpenTime) = YEAR(GETDATE())))
FROM cteTally
WHERE N <= 365
)
,AvailableBlocks AS (
SELECT AvailableBlocks = DATEADD(MINUTE,(N-1)*Q,y.OpenTime)
FROM cteTally c
CROSS JOIN [dbo].[OpenClosePeriod] y
WHERE DATEADD(MINUTE,N*Q,y.OpenTime) <= y.CloseTime
)
SELECT DATEADD(day, 0, DATEDIFF(day, 0, ExplodedDate)) +
DATEADD(day, 0 - DATEDIFF(day, 0, Blocks), Blocks)
FROM DateRange
CROSS JOIN AvailableBlocks