I have a history table (EnrollmentHistory) used to track changes to another table (Enrollment) in my database. The EnrollmentHistory table is populated by a trigger that fires whenever a change is made to the Enrollment Table. The trigger copies the row changed row into the EnrollmentHistory table and then makes the changes to the row in the history table. The enrollment history table only tracks certain attributes for enrollment changes (e.g. changes to the grade are kept, but changes to notes are not). The way the trigger was initially designed incorrectly and it captured any update to the row, including updates where nothing changed or one of the attributes that isn't tracked was changed. The trigger has since been fixed, but there is a lot of duplicate information in the history table.
I am trying to go through and clean-up this table, however I'm not sure the best way I can do that. Since this is a history table, I want to go through an clean up duplicates that occur next to each other chronologically. Here is an example,
LOG_ID ENROLLMENT_ID GRADE CREATE_DATE CHANGE_DATE -------- --------------- ------- ------------------ ------------------ 1 1 1/1/2015 10:00AM 1/1/2015 11:00AM 2 1 A 1/1/2015 11:00AM 1/1/2015 11:02AM 3 1 A 1/1/2015 11:02AM 1/1/2015 11:05AM 4 1 B+ 1/1/2015 11:05AM 1/1/2015 11:07AM 5 1 A 1/1/2015 11:07AM 1/1/2015 11:09AM 6 1 B+ 1/1/2015 11:09AM
LOG_IDs 2 and 3s are duplicates since nothing was changed between the two, so I want to remove Log ID 3. On the other hand, Logs 4 and 6 are not duplicates because there was a change in between (i.e. the Grade was change from B+ -> A -> B+). Is it possible to write a query to go through and clean up this table they way I would like to? I only need to run this once, so it doesn't necessarily need to be efficient.