0

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.

  • 2
    Well, for starters, for anything other than standard SQL, if you are talking about triggers, SPs, &c. - all of these are very RDBMS specific. Firstly, tell us your particular RDBMS, and then post your old and new trigger code. – Vérace Apr 7 '15 at 20:18
0

[should work, make sure you have a backup ;)] I added second enrolment ID, and also a double duplicate...

Declare @Test TABLE (
LOG_ID  INT PRIMARY KEY,
ENROLLMENT_ID INT,
GRADE CHAR(2),
CREATE_DATE DATETIME,   
CHANGE_DATE DATETIME)

INSERT INTO @Test VALUES

(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','1/1/2015 11:09AM'),
(7,2,'B+','1/1/2015 11:05AM','1/1/2015 11:07AM'),
(8,2,'A'    ,'1/1/2015 11:07AM','1/1/2015 11:09AM'),
(9,2,'B+','1/1/2015 11:09AM','1/1/2015 11:09AM'),
(10,2,'B+','1/1/2015 11:09AM','1/1/2015 11:09AM'),
(11,2,'B+','1/1/2015 11:09AM','1/1/2015 11:09AM')

; WITH CTE AS (
SELECT LOG_ID, ENROLLMENT_ID, Grade, ROW_NUMBER() OVER (ORDER BY LOG_ID) TH
FROM @Test T
)

--SELECT DATA.*
--FROM @Test Data
--JOIN (
--SELECT
--  A.Log_ID
--FROM CTE A
--JOIN CTE B
--ON A.ENROLLMENT_ID = B.ENROLLMENT_ID
--AND A.GRADE = B.GRADE
--AND A.TH = B.TH + 1 ) DUPLICATED_DATA
--ON DATA.LOG_ID = DUPLICATED_DATA.LOG_ID

DELETE Data
FROM @Test Data
JOIN (
SELECT
    A.Log_ID
FROM CTE A
JOIN CTE B
ON A.ENROLLMENT_ID = B.ENROLLMENT_ID
AND A.GRADE = B.GRADE
AND A.TH = B.TH + 1 ) DUPLICATED_DATA
ON DATA.LOG_ID = DUPLICATED_DATA.LOG_ID

SELECT * FROM @Test
0

Well, if we assume you're using MS SQL Server, this query would find and delete duplicate rows, leaving the original. You might want to run it against a sample table or in a test environment first and confirm that the results are what you want. Also, you can comment the DELETE row and run the commented SELECT instead to get an idea how much rows the query would actually delete.

DELETE EnrollmentHistory  
--SELECT COUNT(*)   
FROM EnrollmentHistory
LEFT OUTER JOIN (
   SELECT MIN(RowId) AS RowId, ENROLLMENT_ID, GRADE, CREATE_DATE, CHANGE_DATE   --columns to define unique row
   FROM EnrollmentHistory
   GROUP BY ENROLLMENT_ID, GRADE, CREATE_DATE, CHANGE_DATE                      --columns to define unique row
) AS KeepRows ON
   EnrollmentHistory.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.