The sample table (Bookmark is the name of table) content is as below:
Expand|Select|Wrap|Line Numbers
- systemId filename mindatetime maxdatetime
- 70277 monitor_1.dat 2019-04-21 08:00:00 AM 2019-04-21 03:10:00 PM
- 10006 monitor_2.dat 2019-04-25 10:00:00 AM 2019-04-25 11:30:00 AM
- 10006 monitor_3.dat 2019-04-28 08:00:00 AM 2019-04-28 10:00:00 AM
- 10006 monitor_3.dat 2019-04-28 09:00:00 AM 2019-04-28 11:00:00 AM
- 10006 monitor_3.dat 2019-04-28 07:00:00 AM 2019-04-28 04:00:00 PM
- 8368 monitor_1.dat 2019-05-21 11:00:00 AM 2019-05-21 11:30:00 AM
- 8368 monitor_7.dat 2019-05-21 06:00:00 AM 2019-05-21 11:00:00 AM
- 8368 monitor_5.dat 2019-05-23 08:00:00 AM 2019-05-23 10:00:00 AM
The cron job should run on a given schedule to delete the records which are logically redundant.
To explain this let's take the case of systemId '10006' where filename is 'monitor_3.dat' having 3 entries with min and max date timestamp of the same day.
Logically we can delete the entries having mindatetime 08:00:00 AM and 09:00:00 AM, maxdatetime 10:00:00 AM, 11:00:00 AM as that interval is being covered by the other entry which has mindatetime as 7 AM and maxdatetime as 4 PM.
So those entries would fall under this interval and the job should identify such entries in the entire table and delete them.
My resultant output table content in this case should be:
Expand|Select|Wrap|Line Numbers
- systemId filename mindatetime maxdatetime
- 70277 monitor_1.dat 2019-04-21 08:00:00 AM 2019-04-21 03:10:00 PM
- 10006 monitor_2.dat 2019-04-25 10:00:00 AM 2019-04-25 11:30:00 AM
- 10006 monitor_3.dat 2019-04-28 07:00:00 AM 2019-04-28 04:00:00 PM
- 8368 monitor_1.dat 2019-05-21 11:00:00 AM 2019-05-21 11:30:00 AM
- 8368 monitor_7.dat 2019-05-21 06:00:00 AM 2019-05-21 11:00:00 AM
- 8368 monitor_5.dat 2019-05-23 08:00:00 AM 2019-05-23 10:00:00 AM