CODEWITHSUNDEEP
sqlsql-server
Poppy
Poppy

Reputation: 3092

How to find the changes in records in SQL server

I have a table which holds student info. 

+==========================================+
| ID      |  Department     | Date         |
+==========================================+
| 001     | English         | Feb 3 2017   |
| 001     | English         | Feb 4 2017   |
| 001     | Science         | Mar 1 2017   |
| 001     | Maths           | Mar 2 2017   |
| 001     | Maths           | Mar 21 2017  |
| 001     | Maths           | Apr 2 2017   |
| 001     | English         | Apr 7 2017   |
| 002     | Maths           | Feb 1 2017   |
| 002     | Maths           | Apr 7 2017   |
| 003     | Maths           | Apr 3 2017   |
| 003     | Maths           | Apr 7 2017   |
| 004     | Science         | Feb 1 2017   |
| 004     | Science         | Mar 1 2017   |
| 004     | Maths           | Apr 7 2017   |
| 004     | English         | Apr 9 2017   |
+==========================================+

In the above table I need to get the list of student records whenever the student's department preference is changed. There is also a chance that student can change back to the same department again. So for the above sample data, the list of records returned would be

For student 001

| 001     | English         | Feb 4 2017   |
| 001     | Science         | Mar 1 2017   |
| 001     | Maths           | Apr 2 2017   |

002 and 003 Nothing

for 004

| 004     | Science         | Mar 1 2017   |
| 004     | Maths           | Apr 7 2017   |

When I try to apply the logic mentioned in here, partition on doesn't work as the student can come back to the same department again. Kindly help.

Upvotes: 1

Views: 166

Answers (1)

TriV
TriV

Reputation: 5148

You could use LEAD window function - for SQL version 2012 and later...

DECLARE @SampleData AS TABLE 
(
   Id int,
   Department varchar(20),
   [Date] date
)

INSERT INTO @SampleData
VALUES (1,'English', 'Feb 3 2017'),(1,'English', 'Feb 4 2017'),(1,'Science', 'Mar 1 2017'),
(1,'Maths', 'Mar 2 2017'),(1,'Maths', 'Mar 3 2017'),(1,'English', 'Mar 7 2017'),
(2,'Maths', 'Feb 3 2017'),(2,'Maths', 'Feb 4 2017'),
(3,'Maths', 'Feb 3 2017'), (3,'Maths', 'Feb 4 2017'),
(4,'Science', 'Feb 1 2017'), (4,'Science', 'Feb 2 2017'), (4,'Maths', 'Feb 3 2017'),(4,'English', 'Feb 4 2017')

;WITH temps AS 
(
   SELECT sd.*, LEAD(sd.Department, 1) OVER(PARTITION BY id ORDER BY sd.[Date])  AS NextDepartment
   FROM @SampleData sd    
)
SELECT t.id, t.Department,t.[Date] FROM temps t
WHERE t.Department != t.NextDepartment

Demo link: Rextester

Reference link: LEAD - MDSN

For older version you could use OUTER APPLY 

SELECT sd.*
FROM @SampleData sd
OUTER APPLY 
(
   SELECT TOP 1 * FROM @SampleData sd2 WHERE sd.Id = sd2.Id AND sd.[Date] < sd2.[Date]
) nextDepartment
WHERE sd.Department != nextDepartment.Department

Upvotes: 5

Related Questions