Database Challenges

-- File Name: kevin_suchlicki_tsqlchallenge_16_v2.sql
;WITH DateList AS
(
	SELECT PersonID, StartDate Dt, 0 IsEndDate
	FROM @Membership
	UNION
	SELECT PersonID, EndDate, 1
	FROM @Membership
)
, OrderedDateList as
(
	SELECT	PersonID, Dt, IsEndDate
			, Row_Number() OVER (PARTITION BY PersonID ORDER BY IsNull(Dt, '31/12/9999 23:59:59.997')) ord
	FROM	DateList
)
, DateRanges as
(
	SELECT	o.PersonID
			, CASE WHEN o.IsEndDate = 0 THEN o.Dt ELSE o.Dt + 1 END StartDate
			, Max(CASE WHEN o2.IsEndDate = 1 THEN o2.Dt ELSE o2.Dt - 1 END) EndDate
	FROM	OrderedDateList o
			INNER JOIN OrderedDateList o2 ON o2.PersonID = o.PersonID And o2.ord = o.ord + 1
	WHERE	o.Dt Is Not Null
	GROUP BY o.PersonID
			, CASE WHEN o.IsEndDate = 0 THEN o.Dt ELSE o.Dt + 1 END 
)
, DateRangesWithMembership As
(
	SELECT  d.PersonID
			, Convert(char(10), d.StartDate, 103) StartDate, Convert(char(10), d.EndDate, 103) EndDate
			, Stuff((SELECT ' / ' + m.Description
			FROM	@Membership m
			WHERE	m.PersonID = d.PersonID 
					And d.StartDate >= m.StartDate And (d.StartDate <= m.EndDate Or m.EndDate Is Null)
			ORDER BY m.StartDate
			FOR XML PATH('')), 1, 3, '') Membership
	FROM	DateRanges d
)
SELECT	drm.PersonID, n.Surname, n.FirstName, drm.Membership, drm.StartDate, drm.EndDate
FROM	DateRangesWithMembership drm
		INNER JOIN 
			(SELECT DISTINCT PersonID, Surname, FirstName 
			FROM	@Membership) n ON n.PersonID = drm.PersonID
WHERE	drm.Membership Is Not Null
ORDER BY drm.PersonID, Cast(drm.StartDate As datetime)

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.