TSQL Challenge 16 - Find intersections in date ranges and concatenate aggregated labels
Challenge Context
This challenge is derived from a real scenario discussed in a forum. It mainly deals about finding intersections in date ranges.
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
Note: The headers were removed here for presentation reasons.
Sample Data
Use the following script to generate the sample data used for this challenge:
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')
Notes
- Read the Submission Guidelines and make sure that your solution follows them.
- The solution should work on SQL Server 2005, 2008 or later versions
- Use this forum for any questions related to TSQL Challenge #16
About the Authors.