Attached is a simple example of the raw data I receive. I work at corporate for a major auto manufacturer, and we report on the repair orders for customers that come in for service (oil change, scheduled maintenance, etc). With an individual repair order, there are operation codes relating to each service completed for the visit. Think of a grocery store receipt, the receipt has a singular transaction code, and each item you purchased is associated with that transaction code. The same goes for when your vehicle gets serviced, each action on your car is recorded as an individual item.
There are three specific operation codes that I need to track for this use case. 2 output columns: distinct count of the repair order, and the "highest tier operation code" used. The codes are related by a tier system, if you get the lowest service package it is "01", but if you get more than "01" is still recorded, yet "02" is added. If there is only "01" the overall repair order needs to be classified as "01". This same concept goes up to tier "03". There could be 15 entries of "01" on a repair order, 4 entries of "02", and one entry of "03". The distinct repair order would need to be counted as one "03".
Example,
- If a repair order comes through with 13 "01" op-codes and a singular "02" op-code
- The count needs to be 1 distinct repair order
- "02" being higher than "01" the second column needs to read "02" as the highest tier
The data set attached has the input and the requested output. Any help would be appreciated as I have mentally exhausted myself trying to figure out how to solve this seemingly simple problem.
