SQL query to sort out sequence of transaction references and check for data consistency

Hi Experts,

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+PriorRequestNbr
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"

In short:
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.
Sample.xlsx
Paul_ATLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
You MUST tell us what database this is for because solution options differ by dbms vendor. MySQL is way different to MS SQL Server which is way different to Oracle etc.

What data base are you using for this query?
0
PortletPaulfreelancerCommented:
In your sample data the chain number appears to increment by 1 when the service number changes, but it appears to be dependent on row order as well so chain number 4 and 6 are for the same service number. However chain number 4 also spans 2 service numbers which seems to break the rule. Once chain number id known then sequence is a row number within each chain. Clarification of the rues for chain number and sequence number are needed please.  
 
don't understand chain number 4
It is essential to know if the database  you uses supports "window functions"

Until I know more I shall stop with what I present below (the db used below is SQL Server). This s not a solution, just a step toward one.
[SQL Fiddle][1]

**MS SQL Server 2014 Schema Setup**:

    CREATE TABLE Table1
        ([Account] varchar(8), [RequestNbr] int, [PriorRequestNbr] varchar(6), [ServiceCode1] varchar(10), [ServiceCode2] varchar(10), [ChainNbr] int, [Sequence] int, [Check_SC1] varchar(3), [Check_SC2] varchar(3))
    ;
       
    INSERT INTO Table1
        ([Account], [RequestNbr], [PriorRequestNbr], [ServiceCode1], [ServiceCode2], [ChainNbr], [Sequence], [Check_SC1], [Check_SC2])
    VALUES
        ('Account1', 990756, '693110', 'Service_61', 'MS09015281', 1, 1, 'OK', 'NOK'),
        ('Account1', 990680, NULL, 'Service_13', 'CI00136309', 3, 1, 'OK', 'OK'),
        ('Account1', 990653, '968845', 'Service_10', 'CI00114441', 2, 1, 'OK', 'OK'),
        ('Account2', 989311, '737695', 'Service_38', 'CI00006927', 6, 1, 'OK', 'OK'),
        ('Account2', 988534, NULL, 'Service_10', 'CI00134342', 10, 1, 'OK', 'OK'),
        ('Account2', 987851, '976641', 'Service_10', 'CI00092160', 7, 1, 'OK', 'OK'),
        ('Account2', 976641, '849675', 'Service_10', 'CI00092160', 7, 2, 'OK', 'OK'),
        ('Account1', 968845, '926441', 'Service_10', 'CI00114441', 2, 2, 'OK', 'OK'),
        ('Account1', 926441, NULL, 'Service_10', 'CI00114441', 2, 3, 'OK', 'OK'),
        ('Account2', 915525, '328829', 'Service_38', 'CI00010318', 8, 1, 'OK', 'OK'),
        ('Account2', 849675, NULL, 'Service_10', 'CI00092160', 7, 3, 'OK', 'OK'),
        ('Account2', 737695, '275232', 'Service_38', 'CI00006927', 6, 2, 'OK', 'OK'),
        ('Account2', 710815, '613683', 'Service_38', 'CI00031133', 9, 1, 'OK', 'NOK'),
        ('Account1', 707498, '505291', 'Service_37', NULL, 4, 1, 'NOK', 'NOK'),
        ('Account1', 707497, '505291', 'Service_37', NULL, 4, 2, 'NOK', 'NOK'),
        ('Account2', 695311, NULL, 'Service_87', 'CI00023590', 5, 1, 'OK', 'OK'),
        ('Account1', 693110, '529671', 'Service_61', 'MS09015281', 1, 2, 'OK', 'NOK'),
        ('Account2', 613683, '361608', 'Service_38', 'CI00031132', 9, 2, 'OK', 'NOK'),
        ('Account1', 529671, NULL, 'Service_61', NULL, 1, 3, 'OK', 'NOK'),
        ('Account1', 505291, '434966', 'Service_37', NULL, 4, 3, 'NOK', 'NOK'),
        ('Account1', 434966, '395530', 'Service_37', NULL, 4, 4, 'NOK', 'NOK'),
        ('Account1', 395530, '341795', 'Service_37', NULL, 4, 5, 'NOK', 'NOK'),
        ('Account2', 361608, NULL, 'Service_38', 'CI00031133', 9, 3, 'OK', 'NOK'),
        ('Account1', 341795, '299775', 'Service_38', NULL, 4, 6, 'NOK', 'NOK'),
        ('Account2', 328829, NULL, 'Service_38', NULL, 8, 2, 'OK', 'OK'),
        ('Account1', 299775, '203443', 'Service_38', NULL, 4, 7, 'NOK', 'NOK'),
        ('Account2', 275232, NULL, 'Service_38', 'CI00006927', 6, 3, 'OK', 'OK'),
        ('Account1', 203443, '200862', 'Service_38', NULL, 4, 8, 'NOK', 'NOK'),
        ('Account1', 200862, '139036', 'Service_38', NULL, 4, 9, 'NOK', 'NOK'),
        ('Account1', 139036, NULL, 'Service_38', NULL, 4, 10, 'NOK', 'NOK')
    ;
    select
           [Account], [RequestNbr], [PriorRequestNbr], [ServiceCode1], [ServiceCode2]
    into Table2
    from Table1
    order by newid()
    
**Query 1**:

    with CTE as (
          select *
          , row_number() over(order by account, RequestNbr, PriorRequestNbr) rn
          from table2
       )
    select
    *
    from cte

**[Results][2]**:
    |  Account | RequestNbr | PriorRequestNbr | ServiceCode1 | ServiceCode2 | rn |
    |----------|------------|-----------------|--------------|--------------|----|
    | Account1 |     139036 |          (null) |   Service_38 |       (null) |  1 |
    | Account1 |     200862 |          139036 |   Service_38 |       (null) |  2 |
    | Account1 |     203443 |          200862 |   Service_38 |       (null) |  3 |
    | Account1 |     299775 |          203443 |   Service_38 |       (null) |  4 |
    | Account1 |     341795 |          299775 |   Service_38 |       (null) |  5 |
    | Account1 |     395530 |          341795 |   Service_37 |       (null) |  6 |
    | Account1 |     434966 |          395530 |   Service_37 |       (null) |  7 |
    | Account1 |     505291 |          434966 |   Service_37 |       (null) |  8 |
    | Account1 |     529671 |          (null) |   Service_61 |       (null) |  9 |
    | Account1 |     693110 |          529671 |   Service_61 |   MS09015281 | 10 |
    | Account1 |     707497 |          505291 |   Service_37 |       (null) | 11 |
    | Account1 |     707498 |          505291 |   Service_37 |       (null) | 12 |
    | Account1 |     926441 |          (null) |   Service_10 |   CI00114441 | 13 |
    | Account1 |     968845 |          926441 |   Service_10 |   CI00114441 | 14 |
    | Account1 |     990653 |          968845 |   Service_10 |   CI00114441 | 15 |
    | Account1 |     990680 |          (null) |   Service_13 |   CI00136309 | 16 |
    | Account1 |     990756 |          693110 |   Service_61 |   MS09015281 | 17 |
    | Account2 |     275232 |          (null) |   Service_38 |   CI00006927 | 18 |
    | Account2 |     328829 |          (null) |   Service_38 |       (null) | 19 |
    | Account2 |     361608 |          (null) |   Service_38 |   CI00031133 | 20 |
    | Account2 |     613683 |          361608 |   Service_38 |   CI00031132 | 21 |
    | Account2 |     695311 |          (null) |   Service_87 |   CI00023590 | 22 |
    | Account2 |     710815 |          613683 |   Service_38 |   CI00031133 | 23 |
    | Account2 |     737695 |          275232 |   Service_38 |   CI00006927 | 24 |
    | Account2 |     849675 |          (null) |   Service_10 |   CI00092160 | 25 |
    | Account2 |     915525 |          328829 |   Service_38 |   CI00010318 | 26 |
    | Account2 |     976641 |          849675 |   Service_10 |   CI00092160 | 27 |
    | Account2 |     987851 |          976641 |   Service_10 |   CI00092160 | 28 |
    | Account2 |     988534 |          (null) |   Service_10 |   CI00134342 | 29 |
    | Account2 |     989311 |          737695 |   Service_38 |   CI00006927 | 30 |

  [1]: http://sqlfiddle.com/#!6/3c8c28/1
  [2]: http://sqlfiddle.com/#!6/3c8c28/1/0

Open in new window

0
Paul_ATLAuthor Commented:
Hi PortletPaul,

- The table is a standard Oracle. I am using Oracle SQLDeveloper to query it.

- The chain is the continuity of "RequestNbr - PriorRequestNbr"  and it is not related to ServiceCode1 nor ServiceCode2.  Perhaps, it is more clear to see the chain in this image

Explanation of ChainNbr
you can see that
- ChainNbr 1 is basically showing the link of RequestNbr - PriorRequestNbr in this chain 990756 -> 693110 -> 529671
- ChainNbr 2 is similar to ChainNbr 1 but with 990653 -> 968845 -> 926441
- ChainNbr3 has only one record since PriorRequestNbr is NULL (meaning, it is the first request in the chain)
- ChainNbr4 is a bit long. it has a unique point that both 707498 and 707497 refer to the same PriorRequestNbr 505291

In this sample data, Chain 4 is showing Check_SC1 = "NOK" because ServiceCode1 in this chain is not unique.  Check_SC2 = "NOK" because ServiceCode2 is all NULL (it cannot be NULL)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
Thanks, that is much clearer now. One other question is there ANY other columns available? an id? a date_created?

The reason for asking is that tables are "unordered sets" so assuming a  particular row sequence can be replicated isn't a safe assumption.
0
PortletPaulfreelancerCommented:
While I remain a little concerned there is no 100% reliable way to ensure the row sequence remains "as inserted" I believe this query achieves what you are seeking:
 with cte as (
      select
            row_number() over(order by 1) rn
          , case when PriorRequestNbr IS NULL then 1 end end_chain
          , Account, RequestNbr, PriorRequestNbr, ServiceCode1, ServiceCode2
          , Check_SC1, Check_SC2
      from table1
     )
, Chains as (
      select
            sum(coalesce(end_chain,0)) over(order by rn) + 1 Chain
          , cte.*
      from cte
    )
, sequenced as (
    select
           case when PriorRequestNbr IS NULL then Chain-1 else Chain end ChainNbr
         , row_number() over(partition by case when PriorRequestNbr IS NULL then Chain-1 else Chain end order by rn) as SequenceNbr
         , chains.*
    from chains
  )
select 
       Account, RequestNbr, PriorRequestNbr, ServiceCode1, ServiceCode2
     , ChainNbr
     , SequenceNbr
     , case when min(NVL(ServiceCode1,'00')) over(partition by ChainNbr) = max(NVL(ServiceCode1,'01')) over(partition by ChainNbr) then 'OK' else 'NOK' end Check_SC1
     , case when min(NVL(ServiceCode2,'00')) over(partition by ChainNbr) = max(NVL(ServiceCode2,'01')) over(partition by ChainNbr) then 'OK' else 'NOK' end Check_SC2
from sequenced
order by rn

Open in new window

|  ACCOUNT | REQUESTNBR | PRIORREQUESTNBR | SERVICECODE1 | SERVICECODE2 | CHAINNBR | SEQUENCENBR | CHECK_SC1 | CHECK_SC2 |
|----------|------------|-----------------|--------------|--------------|----------|-------------|-----------|-----------|
| Account1 |     990756 |          693110 |   Service_61 |   MS09015281 |        1 |           1 |        OK |       NOK |
| Account1 |     693110 |          529671 |   Service_61 |   MS09015281 |        1 |           2 |        OK |       NOK |
| Account1 |     529671 |          (null) |   Service_61 |       (null) |        1 |           3 |        OK |       NOK |
| Account1 |     990653 |          968845 |   Service_10 |   CI00114441 |        2 |           1 |        OK |        OK |
| Account1 |     968845 |          926441 |   Service_10 |   CI00114441 |        2 |           2 |        OK |        OK |
| Account1 |     926441 |          (null) |   Service_10 |   CI00114441 |        2 |           3 |        OK |        OK |
| Account1 |     990680 |          (null) |   Service_13 |   CI00136309 |        3 |           1 |        OK |        OK |
| Account1 |     707498 |          505291 |   Service_37 |       (null) |        4 |           1 |       NOK |       NOK |
| Account1 |     707497 |          505291 |   Service_37 |       (null) |        4 |           2 |       NOK |       NOK |
| Account1 |     505291 |          434966 |   Service_37 |       (null) |        4 |           3 |       NOK |       NOK |
| Account1 |     434966 |          395530 |   Service_37 |       (null) |        4 |           4 |       NOK |       NOK |
| Account1 |     395530 |          341795 |   Service_37 |       (null) |        4 |           5 |       NOK |       NOK |
| Account1 |     341795 |          299775 |   Service_38 |       (null) |        4 |           6 |       NOK |       NOK |
| Account1 |     299775 |          203443 |   Service_38 |       (null) |        4 |           7 |       NOK |       NOK |
| Account1 |     203443 |          200862 |   Service_38 |       (null) |        4 |           8 |       NOK |       NOK |
| Account1 |     200862 |          139036 |   Service_38 |       (null) |        4 |           9 |       NOK |       NOK |
| Account1 |     139036 |          (null) |   Service_38 |       (null) |        4 |          10 |       NOK |       NOK |
| Account2 |     695311 |          (null) |   Service_87 |   CI00023590 |        5 |           1 |        OK |        OK |
| Account2 |     989311 |          737695 |   Service_38 |   CI00006927 |        6 |           1 |        OK |        OK |
| Account2 |     737695 |          275232 |   Service_38 |   CI00006927 |        6 |           2 |        OK |        OK |
| Account2 |     275232 |          (null) |   Service_38 |   CI00006927 |        6 |           3 |        OK |        OK |
| Account2 |     987851 |          976641 |   Service_10 |   CI00092160 |        7 |           1 |        OK |        OK |
| Account2 |     976641 |          849675 |   Service_10 |   CI00092160 |        7 |           2 |        OK |        OK |
| Account2 |     849675 |          (null) |   Service_10 |   CI00092160 |        7 |           3 |        OK |        OK |
| Account2 |     915525 |          328829 |   Service_38 |   CI00010318 |        8 |           1 |        OK |       NOK |
| Account2 |     328829 |          (null) |   Service_38 |       (null) |        8 |           2 |        OK |       NOK |
| Account2 |     710815 |          613683 |   Service_38 |   CI00031133 |        9 |           1 |        OK |       NOK |
| Account2 |     613683 |          361608 |   Service_38 |   CI00031132 |        9 |           2 |        OK |       NOK |
| Account2 |     361608 |          (null) |   Service_38 |   CI00031133 |        9 |           3 |        OK |       NOK |
| Account2 |     988534 |          (null) |   Service_10 |   CI00134342 |       10 |           1 |        OK |        OK |

Open in new window

Testing performed (and useful as a demo) here:  http://sqlfiddle.com/#!4/226fcf/3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul_ATLAuthor Commented:
Hi PortletPaul,

Yes, there is another column that provide Date/Time of when the RequestNbr is raised.  Initially, I thought it might not be relevant as the sequencing of RequestNbr would be suffice.  I will be updating the SAMPLE.XLSX in an hour to include this date column.

Thank you,
0
PortletPaulfreelancerCommented:
Thats ok. Instead of basing order on "rn" in my solution, replace "rn" with that date column instead. Eg

, Chains as (
      select
            sum(coalesce(end_chain,0)) over(order by rn) + 1 Chain
          , cte.*
      from cte
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.