I need help to write query that will sort out the transactions from a table that contain several transaction records mixed together and once sorted to check for consistency of data captured specific columns.
The table consist of following columns
- ACCOUNT = List of account number (in the sample file we have Account1 and Account2)
- RequestNbr = the current reference of the transaction. Note that if the current request may have prior request(s)
- PrioRequestNbr = the previous reference of the transaction
- ServiceCode1 = The first information to be checked for consistency. It should be the same for all request transactions and cannot be NULL.
- ServiceCode2 = The second information to be checked for consistency. It should be the same for all request transactions and cannot be NULL.
The XLSX attached is the sample of 30 rows where all information are mixed together. Here, I show the records sorted on RequestNbr.
The queries that I am looking for are:
Part 1: Query to structure the transaction
The structure of the records are properly sequenced based on Account+RequestNbr+PriorRe
This query should produce the result by adding columns "ChainNbr" and "Sequence"
The result should be shown in proper sequence (as if it has been sorted by ChainNbr + Sequence)
Part2: Check ServiceCode1 and ServiceCode2 for Consistency
Within the same ChainNbr the value of ServiceCode1 and ServiceCode2 must be consistent. It should show result "OK" or "NOK"
The table that I am looking to check contains tens of thousands of rows without order.
I need SQL query that produce result as if it is sorted by column F and G of the SAMPLE.XLSX attached
Thank you in advance.