Find intersections in date ranges and concatenate aggregated labels

Hello All,

The original source of data will be a table of persons with their member ship:

                 Description  StartDate   EndDate  
---------------------------------------------------                     
18  Smith  John  Poker Club  01/01/2009  NULL
18  Smith  John  Library     05/01/2009  18/01/2009
18  Smith  John  Gym         10/01/2009  28/01/2009
26  Adams  Jane  Pilates     03/01/2009  16/02/2009
 

The challenge will be to find each intersection of membership date ranges for each person and produce for this each unique range:

• a new line with the StartDate and EndDate of the range


• Concatenate in the description the descriptions of the memberships for this range with:
  
  
  
  • “/” as separator
  
  
  • order by membership StartDate
  
  
  
  

This is the table of results expected:

18  Smith  John  Poker Club                  01/01/2009  04/01/2009
18  Smith  John  Poker Club / Library        05/01/2009  09/01/2009
18  Smith  John  Poker Club / Library / Gym  10/01/2009  18/01/2009
18  Smith  John  Poker Club / Gym            19/01/2009  28/01/2009
18  Smith  John  Poker Club                  29/01/2009  NULL
26  Adams  Jane  Pilates                     03/01/2009  16/02/2009

Sample Script:

SET DATEFORMAT dmy
DECLARE @Membership TABLE ( 
        PersonID        int, 
        Surname         nvarchar(16), 
        FirstName       nvarchar(16), 
        Description nvarchar(16), 
        StartDate       datetime, 
        EndDate         datetime)
        
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Poker Club', '01/01/2009', NULL)
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Library', '05/01/2009', '18/01/2009')
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Gym', '10/01/2009', '28/01/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Pilates', '03/01/2009', '16/02/2009')

Note: Make sure that your solution uses a single query that starts either with "SELECT" or "WITH" or ";WITH".


Can you please share suggestions.

Regards,




---

Vaishu