title: How to combine Date periods in SQL and create a composite timeline
body: <p>In a oracle table for each family (unique id) can have several people (unique id) in different relationship for a date range. I would like to get a timeline created to obtain the FamilyType based on combinations of relationship for the time periods. An example is given below for a particular Family.</p>
  
  <pre><code>  P1|-----Head---------------------------------------|
             P2|--Partner--------------|
                      P3|---Child----------------------|
                                P4|---Child------------|
  
      |=Single=|=Couple=|=Family=======|=SingleParent==|
  </code></pre>
  
  <p>Table has columns
  FamilyId, PersonId, Relationship, StartDate, EndDate</p>
  
  <p>Each | is a date (no time portion). The data guarantees that on a given date<br>
  * there will always be one person who is Head.<br>
  * There can be 0 or 1 Partner.<br>
  * There can be 0 or n child.  </p>
  
  <p>The rules are<br>
  * If there is only a Head the FamilyType is Single<br>
  * If there is a Head and a Partner the FamilyType is Couple<br>
  * If there is a Head , a Partner and 1 or more Children the FamilyType is Family<br>
  * If there is a Head and 1 or more Children the FamilyType is SingleParent  </p>
  
  <p>People can join or leave from a family on any date.
  And people can change relationships. So following scenarios are possible</p>
  
  <pre><code>P1|----------Head--------------------|
             P2|----partner------------|---Head--------|
                      P3|---Child----------------------|
                            P4|--Child-----------------|
  
      |=Single=|=Couple=|=Family=======|=SingleParent==|
  
  P1|----------Head--------------------|
             P2|----partner------------|---Head--------|
                      P3|---Child----------------------|
                            P4|--Child-----------------|
                                     p5|---Partner-----|
  
      |=Single=|=Couple=|=Family=======================|
  </code></pre>
  
  <p>How can this be done using SQL in Oracle 11GR2 (working using SQL only and not using procedural code). I am trying to evaluate whether this is best done in SQL or C#. As a curiosity answer specific for SQL Server 2012 is also good to have.</p>
  
  <p>The result should be rows with StartDate, EndDate and FamilyType.</p>