;WITH Tens (n) AS ( SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), RowGenerator AS ( SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM Tens a CROSS JOIN Tens b CROSS JOIN Tens c CROSS JOIN Tens d CROSS JOIN Tens e )SELECT location_id, unoccupied_start_dt = CASE WHEN seq = 1 THEN NULL ELSE unoccupied_start_dt END, unoccupied_end_dt = CASE WHEN seq = [Rows] THEN NULL ELSE unoccupied_end_dt END FROM ( SELECT location_id, unoccupied_start_dt = MIN(Timespot), unoccupied_end_dt = MAX(Timespot), seq = ROW_NUMBER() OVER(PARTITION BY location_id ORDER BY TimeGroup), [Rows] = COUNT(*) OVER(PARTITION BY location_id) FROM ( SELECT s.location_id, s.MIN_start_dt, s.MAX_end_dt, x.Timespot, TimeGroup = DATEADD(minute,1-ROW_NUMBER() OVER(PARTITION BY s.location_id ORDER BY x.Timespot), x.Timespot) FROM (SELECT location_id, MIN_start_dt = MIN(start_dt), MAX_end_dt = MAX(end_dt) FROM #stays GROUP BY location_id) s CROSS APPLY ( SELECT TOP (DATEDIFF(minute,s.MIN_start_dt,s.MAX_end_dt)+3) TimeSpot = DATEADD(minute,n-2,s.MIN_start_dt) FROM RowGenerator ) x WHERE NOT EXISTS (SELECT 1 FROM #stays l WHERE l.location_id = s.location_id AND x.Timespot BETWEEN l.start_dt AND l.end_dt) ) d GROUP BY location_id, TimeGroup) o

-- Q1 DROP TABLE #Temp1; WITH RowGenerator AS ( SELECT n = 1 UNION ALL SELECT n+1 FROM RowGenerator WHERE n < 500000)SELECT nINTO #Temp1 FROM RowGenerator OPTION (MAXRECURSION 0)GO 10-- Q2 DROP TABLE #Temp2; SELECT TOP (500000) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) INTO #Temp2 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n) -- 10 CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (n) -- 100 CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c (n) -- 1000 CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n) -- 10000 CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n) -- 100000 CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n) -- 1000000 GO 10-- Q3 DROP TABLE #Temp3; ;WITH _2 (n) AS (SELECT 0 UNION ALL SELECT 0), _4 (n) AS (SELECT a.n FROM _2 a, _2 b), _16 (n) AS (SELECT a.n FROM _4 a, _4 b), _256 (n) AS (SELECT a.n FROM _16 a, _16 b), _65536 (n) AS (SELECT a.n FROM _256 a, _256 b)SELECT TOP (500000) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))INTO #Temp3 FROM _65536 a, _16 bGO 10