title: Trouble using ROW_NUMBER() OVER (PARTITION BY ...)
body: <p>I'm using SQL Server 2008 R2. I have table called EmployeeHistory with the following structure and sample data:</p>
  
  <pre><code>EmployeeID Date      DepartmentID SupervisorID
  10001      20130101  001          10009
  10001      20130909  001          10019
  10001      20131201  002          10018
  10001      20140501  002          10017
  10001      20141001  001          10015
  10001      20141201  001          10014
  </code></pre>
  
  <p>Notice that the Employee 10001 has been changing 2 departments and several supervisors over time. What I am trying to do is to list the start and end dates of this employee's employment in each Department ordered by the Date field. So, the output will look like this:</p>
  
  <pre><code>EmployeeID DateStart DateEnd  DepartmentID 
  10001      20130101  20131201 001
  10001      20131201  20141001 002
  10001      20141001  NULL     001
  </code></pre>
  
  <p>I intended to use partitioning the data using the following query but it failed. The Department changes from 001 to 002 and then back to 001. Obviously I cannot partition by DepartmentID... I'm sure I'm overlooking the obvious. Any help? Thank you, in advance.</p>
  
  <pre><code>SELECT * ,ROW_NUMBER() OVER (PARTITION BY EmployeeID, DepartmentID
  ORDER BY [Date]) RN FROM EmployeeHistory
  </code></pre>