Removing chronological duplicates from history table in SQL
0
votes
1
answer
53
views
I have a history table, I want to create another view to pick relevant records from this history. Not all records are relevant. I'm after the record changes for certain columns, for this case I care only about Plate and Status, and their changes over time, otherwise they are considered duplicates and should be not be showing in the new query output.
I want to check the chronological changes per each PLATE value (doing a ROW OVER PARTITION BY PLATE), the changes should be compared by column SysloadDaterie order and if current row vs previous row have the same relevant column values, then should be considered duplicate and removed from the output of the query.
In the example, there are 2 plates that have records:
columns with duplicated Status value that are not chronologically next to each other they should stay as they can go back and forth over time. But if statuses are next to each other chronologically should be considered duplicates then.
example:
CREATE TABLE history (
plate_number VARCHAR(20),
status VARCHAR(20),
comments VARCHAR(100),
sysloadDatetime DATETIME
);
-- Insert sample data for two plate numbers with duplicate rows chronologically
INSERT INTO history (plate_number, status, comments, sysloadDatetime) VALUES
('ABC123', 'Active', 'Initial entry', '2024-02-20 08:00:00'),
('ABC123', 'Active', 'Checked at checkpoint A', '2024-02-20 08:15:00'),
('ABC123', 'Active', 'Checked at checkpoint B', '2024-02-20 08:30:00'),
('ABC123', 'Inactive', 'Temporarily deactivated for maintenance', '2024-02-20 09:00:00'),
('ABC123', 'Inactive', 'Awaiting reactivation', '2024-02-20 09:05:00'),
('ABC123', 'Active', 'Reactivated after maintenance', '2024-02-20 10:00:00'),
('ABC123', 'Active', 'Final check completed', '2024-02-20 10:30:00'),
('XYZ789', 'Active', 'Initial entry', '2024-02-21 08:00:00'),
('XYZ789', 'Active', 'Checked at checkpoint A', '2024-02-21 08:15:00'),
('XYZ789', 'Inactive', 'Temporarily deactivated for maintenance', '2024-02-21 08:30:00'),
('XYZ789', 'Inactive', 'Awaiting reactivation', '2024-02-21 09:00:00'),
('XYZ789', 'Active', 'Reactivated after maintenance', '2024-02-21 09:30:00'),
('XYZ789', 'Active', 'Final check completed', '2024-02-21 10:00:00');
-- Query: Select all records ordered chronologically
SELECT * FROM history
ORDER BY sysloadDatetime;
History table output:
plate_number | status | comments | sysloadDatetime
-------------|----------|------------------------------------------|---------------------
ABC123 | Active | Initial entry | 2024-02-20 08:00:00
ABC123 | Active | Checked at checkpoint A | 2024-02-20 08:15:00
ABC123 | Active | Checked at checkpoint B | 2024-02-20 08:30:00
ABC123 | Inactive | Temporarily deactivated for maintenance | 2024-02-20 09:00:00
ABC123 | Inactive | Awaiting reactivation | 2024-02-20 09:05:00
ABC123 | Active | Reactivated after maintenance | 2024-02-20 10:00:00
ABC123 | Active | Final check completed | 2024-02-20 10:30:00
XYZ789 | Active | Initial entry | 2024-02-21 08:00:00
XYZ789 | Active | Checked at checkpoint A | 2024-02-21 08:15:00
XYZ789 | Inactive | Temporarily deactivated for maintenance | 2024-02-21 08:30:00
XYZ789 | Inactive | Awaiting reactivation | 2024-02-21 09:00:00
XYZ789 | Active | Reactivated after maintenance | 2024-02-21 09:30:00
XYZ789 | Active | Final check completed | 2024-02-21 10:00:00
Desired output:
plate_number | status | comments | sysloadDatetime
-------------|----------|------------------------------------------|---------------------
ABC123 | Active | Initial entry | 2024-02-20 08:00:00
ABC123 | Inactive | Temporarily deactivated for maintenance | 2024-02-20 09:00:00
ABC123 | Active | Reactivated after maintenance | 2024-02-20 10:00:00
XYZ789 | Active | Initial entry | 2024-02-21 08:00:00
XYZ789 | Inactive | Temporarily deactivated for maintenance | 2024-02-21 08:30:00
XYZ789 | Active | Reactivated after maintenance | 2024-02-21 09:30:00
Asked by nrod88
(1 rep)
Feb 21, 2025, 05:53 AM
Last activity: Feb 23, 2025, 01:26 PM
Last activity: Feb 23, 2025, 01:26 PM