I have some data that includes two columns for dates, and I want to retrieve - based on these two columns - all the instances where an illegal operation has occured. An operation is illegal if the value in Col B
breaks chronology in the column.
Structure
* Col A * Col B *
* Opened * Moved *
Row * --------------------------*
1 * 06/20 10:00 * 06/22 08:10 *
2 * 06/20 10:30 * 06/22 08:40 *
3 * 06/21 10:00 * 06/22 08:30 *
4 * 06/21 11:00 * 06/22 08:50 *
The table is sorted on Col A
, from smallest to largest.
The rule is that the oldest open item should be moved first.
Thus, moving some arbitrary item that isn't the oldest open item is illegal.
Example
The item in row 3 was moved at 08:30, but at that time there was an older item (row 2) that should have been moved first. That means that row 3 represents an illegal operation and should be marked or returned.
My case
My data set is in the range of millions of rows. I need an accurate method for finding these illegal operations, but I am a little stuck on how to approach the problem.
The most workable idea I've had so far, I think, is to build a helper column where the data from Col B
is sorted lowest to highest, compare this step by step with the original column, and shift the data downwards every time the data doesn't match. This will leave the helper column with blanks indicating illegal operations in the corresponding row in the original dataset.
Another idea was to compare the current, preceeding and following dates - but this has seemingly expensive weaknesses. Although rare it's conceptually possible that several illegal operations can immediately follow each other, meaning that I have to compare some arbitrary higher number of dates (let's say 15 instead of 2 (the preceeding + following)) for each step in a loop.
While one of these sub-optimal ideas probably will do the work, in the sense that the job gets done eventually, I struggle with the thought that there isn't a better way to solve this.