title: How to track changes in records in SQL Server?
body: <p>I have the following table which has tracking records of all students.</p>
  
  <pre><code>|==========================================|
  | ID      |  Department     | Date         |
  |==========================================|
  | 001     | English         | Feb 3 2017   |
  | 001     | English         | Feb 4 2017   |
  | 001     | Science         | Mar 1 2017   |
  | 001     | Science         | Apr 2 2017   |
  | 001     | Maths           | Apr 7 2017   |
  | 002     | Maths           | Feb 1 2017   |
  | 002     | Maths           | Apr 7 2017   |
  | 003     | Maths           | Apr 3 2017   |
  | 004     | Science         | Feb 1 2017   |
  | 004     | Maths           | Apr 7 2017   |
  |==========================================| 
  </code></pre>
  
  <p>I need to fetch the previous record just before when the student has changed the department. For the example above, the record set returned should be </p>
  
  <p><strong>For 001,</strong></p>
  
  <pre><code>| 001     | English         | Feb 4 2017   |
  | 001     | Science         | Apr 2 2017   |
  </code></pre>
  
  <p><strong>For 002 and 003</strong></p>
  
  <p>No changes</p>
  
  <p><strong>For 004</strong></p>
  
  <pre><code>| 004     | Science         | Feb 1 2017   |
  </code></pre>
  
  <p>There is also a possibility that the same user can change back to the same department. for example, user001 can change from dept a to dept b to dept c and back to dept a. I have read about T-SQL send and receive. But not sure if that would help in this scenario. Please help.</p>