How to track changes in records in SQL Server? -

i have following table has tracking records of students.

|==========================================| | 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   | |==========================================|  

i need fetch previous record before when student has changed department. example above, record set returned should

for 001,

| 001     | english         | feb 4 2017   | | 001     | science         | apr 2 2017   | 

for 002 , 003

no changes

for 004

| 004     | science         | feb 1 2017   | 

there possibility same user can change same department. example, user001 can change dept dept b dept c , dept a. have read t-sql send , receive. not sure if in scenario. please help.

one way use row_number function partitioning detect when value of department column changes.

sample data

declare @t table (id int, department nvarchar(100), dt date); insert @t (id, department, dt) values (1, 'english', 'feb 3 2017'), (1, 'english', 'feb 4 2017'), (1, 'science', 'mar 1 2017'), (1, 'science', 'apr 2 2017'), (1, 'maths  ', 'apr 7 2017'), (2, 'maths  ', 'feb 1 2017'), (2, 'maths  ', 'apr 7 2017'), (3, 'maths  ', 'apr 3 2017'), (4, 'science', 'feb 1 2017'), (4, 'maths  ', 'apr 7 2017'); 

query

with cte (     select         id         ,department         ,dt         ,row_number() on (partition id, department order dt desc) rnpart         ,row_number() on (partition id order dt desc) rnid     @t ) select     id     ,department     ,dt cte     rnpart = 1     , rnid <> 1 order     id     ,dt ; 

result

+----+------------+------------+ | id | department |     dt     | +----+------------+------------+ |  1 | english    | 2017-02-04 | |  1 | science    | 2017-04-02 | |  4 | science    | 2017-02-01 | +----+------------+------------+