Database Challenges

-- File Name: mark_cowne_tsqlchallenge_16.sql
WITH Starts(PersonID,Surname,FirstName,StartDate) AS (
 SELECT PersonID,
        Surname,
        FirstName,
        StartDate
 FROM @Membership
 UNION ALL
 SELECT a.PersonID,
        a.Surname,
        a.FirstName,
        a.EndDate+1
 FROM @Membership a
 WHERE EXISTS (SELECT * FROM @Membership b
               WHERE b.PersonID=a.PersonID
                 AND a.EndDate+1 BETWEEN b.StartDate AND COALESCE(b.EndDate,a.EndDate+1))),
                 
Ends(PersonID,EndDate) AS (
 SELECT PersonID,
        EndDate
 FROM @Membership
 UNION ALL
 SELECT a.PersonID,
        a.StartDate-1
 FROM @Membership a
 WHERE EXISTS (SELECT * FROM @Membership b
               WHERE b.PersonID=a.PersonID
                 AND a.StartDate-1 BETWEEN b.StartDate AND COALESCE(b.EndDate,a.StartDate-1)))
                 
SELECT a.PersonID,
       a.Surname,
       a.FirstName,
       STUFF((SELECT '/ ' + c.Description AS "data()"
              FROM @Membership c
              WHERE c.PersonID=a.PersonID
                AND c.StartDate<=COALESCE(MIN(b.EndDate),c.StartDate)
                AND COALESCE(c.EndDate,a.StartDate)>=a.StartDate
              ORDER BY c.StartDate
              FOR XML PATH('')
        ),1,2,'') AS Descriptions,
       a.StartDate,
       MIN(b.EndDate) AS EndDate
FROM Starts a
INNER JOIN Ends b ON b.PersonID=a.PersonID AND COALESCE(b.EndDate,'20991231')>=a.StartDate
GROUP BY a.PersonID,a.Surname,a.FirstName,a.StartDate
ORDER BY a.PersonID,a.StartDate

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.