title: SQL join two record into one row with multiple column
body: <p>i want to join two record (from same table) into one row with multiple column.</p>
  
  <p>employment history structure as follows:</p>
  
  <pre><code>StaffID     StartDate       EndDate         DeptID
  ==================================================
  1           2010-10-01      2011-01-19      1
  1           2011-01-20      2012-12-31      2
  1           2013-01-01      2013-05-29      4
  </code></pre>
  
  <p>how can i join the two rows into one row if same StaffID and the 2nd record startdate is 1 day after the enddate of 1st record (continuous employment)</p>
  
  <p>the output should like this</p>
  
  <pre><code>StaffID     EffectiveDate   New_DeptID      Prev_DeptID     
  ==================================================
  1           2011-01-20      2               1
  1           2013-01-01      4               2
  </code></pre>
  
  <p>the following is my sql statement but it doesn't work</p>
  
  <pre><code>  select distinct
      ca1.StaffID,
      ca1.ProjectDepartment as Prev_DeptID, ca1.StartDate, ca1.EndDate, 
      ca2.ProjectDepartment as New_DeptID, ca2.StartDate, ca2.EndDate
  from 
  emp_hist as ca1,
  emp_hist as ca2
  where 
      (ca1.StaffID = ca2.StaffID) 
      and ca1.StartDate&lt;&gt;ca2.StartDate
      and ca1.EndDate &lt;&gt;ca2.EndDate
      and  ca2.startdate= DATEADD(day, 1, ca1.enddate)
  </code></pre>
  
  <p>for example, 
  two records (true data) in the table:</p>
  
  <pre><code>StaffID     StartDate                   EndDate                     DeptID
  ===========================================================================
  1           2010-04-12 12:00:00.000     2013-02-28 00:00:00.000     1
  1           2013-03-01 12:00:00.000     2013-08-29 11:02:59.877     2
  </code></pre>
  
  <p>i cannot retrieve this record by using my sql statement</p>