365 Days of Daily Coding: Day 133

I am experiencing a bit of downtime today mainly because I think I might have a brain fog. Brain fog is when you cannot concentrate well and easily feel tired and fatigued. On a brighter note, I am actually quite excited for tomorrow since tomorrow is a public holiday. This was announced only yesterday after the state I live in won the Malaysian Football Cup. Awesome, isn’t it?

I had a hard time coming up with the solution as well as understanding the solution. I will try again tomorrow.

Puzzle 19: Back to the Future

I tried the solution in the live oracle environment here.

WITH cte_TimePeriod_Merge AS
(
SELECT	a.StartDate, MIN(b.EndDate) AS EndDate
FROM	(SELECT	DISTINCT
				t1.StartDate
		FROM	##TimePeriods t1
		) AS a INNER JOIN
		(
		SELECT	t3.EndDate
		FROM	##TimePeriods AS t3 LEFT OUTER JOIN
				##TimePeriods AS t4 ON t3.EndDate >= t4.StartDate AND
										t3.EndDate < t4.EndDate
		GROUP BY t3.EndDate
		HAVING COUNT(t4.StartDate) = 0
		) AS b ON a.StartDate <= b.EndDate
GROUP BY a.StartDate
)
SELECT	MIN(StartDate) as StartDate,
		MAX(EndDate) as EndDate
FROM	cte_TimePeriod_Merge
GROUP BY EndDate;