title: Merging records with consecutive dates in SQL
body: <p>I have the following table schema:</p>
  
  <pre><code>RecordId    EmpID       AbsCode DateFrom    DateTo
  ---------------------------------------------------------------
  666542      1511        AB      09/11/2011  10/11/2011
  666986      1511        AB      11/11/2011  11/11/2011
  666996      1511        EL      13/11/2011  17/11/2011
  755485      1787        SL      01/11/2011  14/11/2011
  758545      1787        SL      15/11/2011  26/11/2011
  796956      1954        AB      09/11/2011  09/11/2011
  799656      1367        AB      09/11/2011  09/11/2011
  808845      1527        EL      16/11/2011  16/11/2011
  823323      1527        EL      17/11/2011  17/11/2011
  823669      1527        EL      18/11/2011  18/11/2011
  899555      1123        AB      09/11/2011  09/11/2011
  990990      1511        AB      12/11/2011  12/11/2011
  </code></pre>
  
  <p>As you can see, the data is entered separately for the same employee. Let's say he reported SL (Sick Leave) for one day, the data is entered, Then he called next day to report another sick leave for two days..etc. Now what I want is to merge these entries when presenting it to the client so that all consecutive Absences with the same absence code will be merged. For example the table above should look exactly like this:</p>
  
  <pre><code>EmpID       AbsCode DateFrom    DateTo
  -------------------------------------------------------------
  1511        AB      09/11/2011  12/11/2011
  1511        EL      13/11/2011  17/11/2011
  1787        SL      01/11/2011  26/11/2011
  1954        AB      09/11/2011  09/11/2011
  1367        AB      09/11/2011  09/11/2011
  1527        EL      16/11/2011  18/11/2011
  1123        AB      09/11/2011  09/11/2011
  </code></pre>
  
  <p>I am not an SQL guy, I can do it using a loop in C# to iterate a <code>DataSet</code> or <code>DataReader</code> but I hope to do this with T-SQL in a stored proc. I have found similar questions in StackOverFlow and checked them all, None of them applies for the above sample table.</p>
  
  <p><strong>EDIT:</strong>
  Sometimes I will Have a situations like this:</p>
  
  <pre><code>RecordId    EmpID       AbsCode DateFrom    DateTo
  ---------------------------------------------------------------
  666542      1511        AB      09/11/2011  10/11/2011
  666986      1511        AB      11/11/2011  25/12/2011
  </code></pre>
  
  <p>As you can see, this Employee had Absence from 9/11/2011 (d/M/yyyy) to (25/12/2011) but the client requested to have Absence list from 1st of December until 31st of December, So the result should be:</p>
  
  <pre><code>EmpID       AbsCode DateFrom    DateTo
  -------------------------------------------------------------
  1511        AB      01/12/2011  12/11/2011
  </code></pre>
  
  <p>So basically, It will show the result according to parameters supplied (from, to). If the record stared before the requested period, It will show it but at the same time it will show the start of the record according to the parameters supplied, Same applies from records endings after the (from, to) parameters.</p>