I've been tasked with organizing a list of scan entries so they can be better analyzed by managers. The list looks similar to this:
+-------------------+-------------------------+
| SCAN | TIMESTAMP |
+-------------------+-------------------------+
| JOHN&000123 | 2020-02-12 12:40:31.000 |
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 |
| JOHN&FINISH BUILD | 2020-02-12 12:49:26.000 |
| JOHN&002124 | 2020-02-12 12:49:27.000 |
| JOHN&321 ABC-712B | 2020-02-12 12:49:30.000 |
| JOHN&FINISH BUILD | 2020-02-12 13:00:55.000 |
| GINA&000192 | 2020-02-12 13:00:57.000 |
| GINA&182 BAH-Q293 | 2020-02-12 13:00:58.000 |
| GINA&FINISH BUILD | 2020-02-12 13:07:58.000 |
| GINA&FINISH BUILD | 2020-02-12 13:07:59.000 |
| JOHN&000192 | 2020-02-12 13:08:05.000 |
| JOHN&127 BAH-Q019 | 2020-02-12 13:08:08.000 |
| JOHN&FINISH BUILD | 2020-02-12 13:11:14.000 |
| MIKE&000192 | 2020-02-12 13:11:19.000 |
| MIKE&289 BAH-Q019 | 2020-02-12 13:11:21.000 |
| MIKE&378 BAH-Q019 | 2020-02-12 13:11:28.000 |
| MIKE&FINISH BUILD | 2020-02-12 13:21:50.000 |
+-------------------+-------------------------+
Each scan has a Username concatenated with some sort of code. Purely numeric codes, e.g. 000123, are Order #s. Longer codes, with alphanumeric characters, e.g. 123 ABC-1647, are Item #s. And Finish Build means the order was assembled. Orders are all limited to one item at a time.
Ideally, a standard set of data would look something like this:
+-------------------+-------------------------+
| SCAN | TIMESTAMP |
+-------------------+-------------------------+
| JOHN&000123 | 2020-02-12 12:40:31.000 | Order
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 | Item
| JOHN&FINISH BUILD | 2020-02-12 12:49:26.000 | Finish
+-------------------+-------------------------+
But occasionally we'll get sets like this:
+-------------------+-------------------------+
| SCAN | TIMESTAMP |
+-------------------+-------------------------+
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 | Item
| JOHN&000123 | 2020-02-12 12:40:31.000 | Order
| JOHN&FINISH BUILD | 2020-02-12 12:49:26.000 | Finish
+-------------------+-------------------------+
+-------------------+-------------------------+
| SCAN | TIMESTAMP |
+-------------------+-------------------------+
| JOHN&123 ABC-1647 | 2020-02-12 12:40:31.000 | Order
| JOHN&000123 | 2020-02-12 12:40:31.000 | Item
| JOHN&321 ABC-712B | 2020-02-12 12:49:26.000 | Order
+-------------------+-------------------------+
I want to make this system as easy and as helpful as possible. We can train our employees to be more careful with the scanner, but mistakes happen and I wanted to know what would be the best way to organize this information for all parties.
Currently, after learning more about T-SQL, I have arranged the information to look more like this:
+-------------------------+----------+----------------------+----------+--------+----------------+-------+
| ScanTimestamp | Username | ScanValue | ScanType | IDCol | IsPossibleDupe | SetID |
+-------------------------+----------+----------------------+----------+--------+----------------+-------+
| 2020-02-12 07:00:03.000 | JOHN | 000123 | ORDER | 688 | NULL | 1 |
| 2020-02-12 07:00:03.000 | JOHN | 123 ABC-1647 | ITEM | 689 | NULL | 1 |
| 2020-02-12 07:09:09.000 | JOHN | FINISH BUILD | FINISH | 690 | NULL | 1 |
| 2020-02-12 07:09:10.000 | JOHN | 002124 | ORDER | 691 | NULL | 2 |
| 2020-02-12 07:09:11.000 | JOHN | 321 ABC-712B | ITEM | 692 | NULL | 2 |
| 2020-02-12 07:24:06.000 | JOHN | FINISH BUILD | FINISH | 693 | NULL | 2 |
| 2020-02-12 07:24:08.000 | GINA | 000192 | ORDER | 694 | NULL | 3 |
| 2020-02-12 07:24:10.000 | GINA | 182 BAH-Q293 | ITEM | 695 | NULL | 3 |
| 2020-02-12 07:45:06.000 | GINA | FINISH BUILD | FINISH | 696 | NULL | 3 |
| 2020-02-12 07:45:12.000 | GINA | FINISH BUILD | FINISH | 697 | 1 | NULL |
| 2020-02-12 07:45:13.000 | JOHN | 000192 | ORDER | 698 | NULL | 4 |
| 2020-02-12 07:45:14.000 | JOHN | 127 BAH-Q019 | ITEM | 699 | NULL | 4 |
| 2020-02-12 07:56:36.000 | JOHN | FINISH BUILD | FINISH | 700 | NULL | 4 |
| 2020-02-12 07:56:39.000 | MIKE | 000192 | ORDER | 701 | NULL | 5 |
| 2020-02-12 07:56:40.000 | MIKE | 289 BAH-Q019 | ITEM | 702 | NULL | 5 |
| 2020-02-12 07:56:47.000 | MIKE | 378 BAH-Q019 | ITEM | 703 | NULL | 5 |
| 2020-02-12 08:30:11.000 | MIKE | FINISH BUILD | FINISH | 704 | NULL | 6 |
+-------------------------+----------+----------------------+----------+--------+----------------+-------+
I feel like this is a bit better to read, but I feel like we can do better. You can also see from the example above that there are some "Sets" of data that do not follow the expected format of Order -> Item -> Finish. I am still building out how to handle some of these scenarios but some are just completely ambiguous and require manager input, e.g. Order#, Item#, Item#, Finish - There can only be one item per order, so which is the correct item?
I was hoping to eventually condense the ORDER ITEM FINISH rows into their own columns, but I wanted some input before I put more time into whether this is even a good approach to this problem:
+-------------------------+-------------------------+-------------------------+----------+-----------+-----------------+----------------+-------+
| ItemTime | OrderTime | FinishTime | Username | Item# | Order# | IsPossibleDupe | SetID |
+-------------------------+-------------------------+-------------------------+----------+-----------+-----------------+----------------+-------+
| 2020-02-12 07:00:03.000 | 2020-02-12 07:00:03.000 | 2020-02-12 07:09:09.000 | JOHN | 000123 | 123 ABC-1647 | NULL | 1 |
+-------------------------+-------------------------+-------------------------+----------+-----------+-----------------+----------------+-------+