Analyzing the Differences entered by a coder and an auditor on a transaction

Hellothere8028 761 Reputation points
2020-11-25T12:15:36.03+00:00

Hi All,

Hope you are doing well!..I am trying to extract the difference in the dcode entered by a coder and an auditor on a transaction (a transaction is represented by a combination of Billid and ctextid)..Going by the created date time for a transaction the coder enters the dcode at the earliest datetime for a transaction and the auditor enters at a later created time for the same transaction.There are only two rows for a single transaction -the earliest created datetime has the JSON (dcodes) entered by the coder and the later created datetime has the JSON(dcodes) entered by the auditor..Now I need to pick up the codername as the first user (earliest created datetime )in a transaction.Now I need to compare the earliest created JSON with the second created JSON( and pull out what dcodes were entered by the coder was corrected by the auditor ,what dcodes was deleted by the auditor,what dcodes was added by the auditor (in only this case the code entered by the auditor needs to be picked up as it is a newly entered code ,in the previous two cases the dcode by the coder needs to be picked up)..Also the sequence does not matter if the dcodes are present in both the coder and auditor JSON...Please find below the input and output DDL...Can you please help me..

Input table
create table ##input1
(Billid int,
Ctextid int,
info JSON,
user varchar(30),
created datetime2
)
insert into ##input1 values
"('2132','91156','[
{
""description"": ""fabula "",
""dCode"": ""8901"",
""CodeId"": ""90001"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""EXper "",
""dCode"": ""9034"",
""CodeId"": ""88343"",
""messages"": [
""""
],
""Number"": 2
}
]','amt1','08/03/2020 17:07'),"
"('2132','91156','[
{
""description"": ""fabula "",
""dCode"": ""8901"",
""CodeId"": ""90001"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""EXper "",
""dCode"": ""9034"",
""CodeId"": ""88343"",
""messages"": [
""""
],
""Number"": 2
},
{
""description"": ""siluka "",
""dCode"": ""887756"",
""CodeId"": ""883773"",
""messages"": [
""""
],
""Number"": 3
}
]','all1','08/03/2020 21:07'),"
"('5678','99344','[
{
""description"": ""TORYA "",
""dCode"": ""99002"",
""CodeId"": ""988332"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""triact "",
""dCode"": ""90774"",
""CodeId"": ""7800034"",
""messages"": [
""""
],
""Number"": 2
}
]','jk1','08/04/2020 18:07'),"
"('5678','99344','[
{
""description"": ""TORYA "",
""dCode"": ""99002"",
""CodeId"": ""988332"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""triact "",
""dCode"": ""90974"",
""CodeId"": ""78034"",
""messages"": [
""""
],
""Number"": 2
}
]','jk2','08/05/2020 18:07'),"
"('7789','60045','[
{
""description"": ""ABNORMAL FINDINGS HELA "",
""dCode"": ""Z003345"",
""CodeId"": ""288897"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""IMPACTED BILATERAL "",
""dCode"": ""U8923"",
""CodeId"": ""7324"",
""messages"": [
""""
],
""Number"": 2
},
{
""description"": ""IMMUNIZATION "",
""dCode"": ""H678"",
""CodeId"": ""26519"",
""messages"": [
""""
],
""Number"": 3
},
{
""description"": "" RUPT EAR DRUM "",
""dCode"": ""I0892"",
""CodeId"": ""567123"",
""messages"": [
""""
],
""Number"": 4
},
{
""description"": ""CHILDHOOD FEVER "",
""dCode"": ""Y98620"",
""CodeId"": ""55467"",
""messages"": [
""""
],
""Number"": 5
}
]','ec1','08/07/2020 6:07'),"
"('7789','60045','[
{
""description"": ""ABNORMAL FINDINGS HELA "",
""dCode"": ""Z8897645"",
""CodeId"": ""288897"",
""messages"": [
""""
],
""Number"": 1
}, {
""description"": ""IMMUNIZATION "",
""dCode"": ""H67891"",
""CodeId"": ""26519"",
""messages"": [
""""
],
""Number"": 2
},
{
""description"": "" RUPT EAR DRUM "",
""dCode"": ""I0892"",
""CodeId"": ""567123"",
""messages"": [
""""
],
""Number"": 3
},
{
""description"": ""Kirolo substaniss FEVER "",
""dCode"": ""J18907"",
""CodeId"": ""66712"",
""messages"": [
""""
],
""Number"": 4
}','ec2','08/07/2020 17:07')

Output table
create table ##output1
(billid int,
ctextid int,
codername varchar(30),
correctedcode varchar(100),
deletedcode varchar(100),
addedcode varchar(100)
)
insert into ##output1 values
('2132','91156','amt1','','','887756'),
('5678','99344','jk1','90774','',''),
('7789','60045','ec1','Z003345,H678','U8923,Y98620','J18907')

Thanks,
Arun

{count} votes

Accepted answer
  1. Viorel 88,656 Reputation points
    2020-11-25T16:35:47.11+00:00

    Check a possible solution made in T-SQL for Microsoft SQL Server:

    drop table if exists ##input1
    drop table if exists ##output1
    
    create table ##input1
    (Billid int,
    Ctextid int,
    info varchar(max),
    [user] varchar(30),
    created datetime2
    )
    
    insert into ##input1 values
    ('2132','91156','[
    {
    "description": "fabula ",
    "dCode": "8901",
    "CodeId": "90001",
    "messages": [
    ""
    ],
    "Number": 1
    },
    {
    "description": "EXper ",
    "dCode": "9034",
    "CodeId": "88343",
    "messages": [
    ""
    ],
    "Number": 2
    }
    ]','amt1','08/03/2020 17:07'),
    ('2132','91156','[
    {
    "description": "fabula ",
    "dCode": "8901",
    "CodeId": "90001",
    "messages": [
    ""
    ],
    "Number": 1
    },
    {
    "description": "EXper ",
    "dCode": "9034",
    "CodeId": "88343",
    "messages": [
    ""
    ],
    "Number": 2
    },
    {
    "description": "siluka ",
    "dCode": "887756",
    "CodeId": "883773",
    "messages": [
    ""
    ],
    "Number": 3
    }
    ]','all1','08/03/2020 21:07'),
    ('5678','99344','[
    {
    "description": "TORYA ",
    "dCode": "99002",
    "CodeId": "988332",
    "messages": [
    ""
    ],
    "Number": 1
    },
    {
    "description": "triact ",
    "dCode": "90774",
    "CodeId": "7800034",
    "messages": [
    ""
    ],
    "Number": 2
    }
    ]','jk1','08/04/2020 18:07'),
    ('5678','99344','[
    {
    "description": "TORYA ",
    "dCode": "99002",
    "CodeId": "988332",
    "messages": [
    ""
    ],
    "Number": 1
    },
    {
    "description": "triact ",
    "dCode": "90974",
    "CodeId": "78034",
    "messages": [
    ""
    ],
    "Number": 2
    }
    ]','jk2','08/05/2020 18:07'),
    ('7789','60045','[
    {
    "description": "ABNORMAL FINDINGS HELA ",
    "dCode": "Z003345",
    "CodeId": "288897",
    "messages": [
    ""
    ],
    "sequenceNumber": 1
    },
    {
    "description": "IMPACTED BILATERAL ",
    "dCode": "U8923",
    "CodeId": "7324",
    "messages": [
    ""
    ],
    "sequenceNumber": 2
    },
    {
    "description": "IMMUNIZATION ",
    "dCode": "H678",
    "CodeId": "26519",
    "messages": [
    ""
    ],
    "sequenceNumber": 3
    },
    {
    "description": " RUPT EAR DRUM ",
    "dCode": "I0892",
    "CodeId": "567123",
    "messages": [
    ""
    ],
    "sequenceNumber": 4
    },
    {
    "description": "CHILDHOOD FEVER ",
    "dCode": "Y98620",
    "CodeId": "55467",
    "messages": [
    ""
    ],
    "sequenceNumber": 5
    }
    ]','ec1','08/07/2020 6:07'),
    ('7789','60045','[
    {
    "description": "ABNORMAL FINDINGS HELA ",
    "dCode": "Z8897645",
    "CodeId": "288897",
    "messages": [
    ""
    ],
    "sequenceNumber": 1
    }, {
    "description": "IMMUNIZATION ",
    "dCode": "H67891",
    "CodeId": "26519",
    "messages": [
    ""
    ],
    "sequenceNumber": 2
    },
    {
    "description": " RUPT EAR DRUM ",
    "dCode": "I0892",
    "CodeId": "567123",
    "messages": [
    ""
    ],
    "sequenceNumber": 3
    },
    
    {
    "description": "Kirolo substaniss FEVER ",
    "dCode": "J18907",
    "CodeId": "66712",
    "messages": [
    ""
    ],
    "sequenceNumber": 4
    }]','ec2','08/07/2020 17:07')
    
    ---
    
    ;
    with Q1 as
    (
        select Billid, Ctextid, [user], Created, [Description], p.dCode, 
            dense_rank() over (partition by Billid, Ctextid order by Created) as rn
        from ##input1
        cross apply openjson(info) with
            (
                dCode varchar(30) '$.dCode',
                [Description] varchar(30) '$.description'
            ) p
    ),
    R1 as
    (
        select * from Q1 where rn = 1
    ),
    R2 as
    (
        select * from Q1 where rn = 2
    ),
    Q2 as
    (
        select coalesce(R1.Billid, R2.Billid) as Billid, coalesce(R1.Ctextid, R2.Ctextid) as Ctextid, 
           R1.[Description] as [Description1], R1.dCode as dCode1,
           R2.[Description] as [Description2], R2.dCode as dCode2
        from R1 
        full outer join R2
        on R2.Billid = R1.Billid 
        and R2.Ctextid = R1.Ctextid 
        and R2.[Description] = R1.[Description]
    )
    select Billid, Ctextid,
        (select top(1) [user] from R1 where Billid = t.Billid and Ctextid = t.Ctextid) as codername,
        coalesce( string_agg(case when dCode1 <> dCode2 then dCode1 end, ', '), '') as correctedcode, 
        coalesce( string_agg(case when dCode2 is null then dCode1 end, ', '), '') as deletedcode, 
        coalesce( string_agg(case when dCode1 is null then dCode2 end, ', '), '') as addedcode 
    from Q2 as t
    group by Billid, Ctextid
    order by Billid
    

    Results:

    Billid  Ctextid  codername  correctedcode  deletedcode    addedcode
    2132    91156    amt1                                     887756
    5678    99344    jk1        90774
    7789    60045    ec1        Z003345, H678  U8923, Y98620  J18907
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,131 Reputation points
    2020-11-26T08:44:16.72+00:00

    Hi @Hellothere8028 ,

    Viorel-1's answer should be working to you.

    In addition, what is the version of your SQL Server?

    STRING_AGG function is applied to SQL Server 2017 and later.

    If your version is SQL Server 2016 or before, you could try with below last part of Viorel's query.

    select Billid, Ctextid,  
         (select top(1) [user] from R1 where Billid = t.Billid and Ctextid = t.Ctextid) as codername,  
       ISNULL(STUFF(  
                    (SELECT   
                    ',' + case when dCode1 <> dCode2 then dCode1 end  
               from Q2 t1  
        where t1.Billid=t.Billid  
        FOR XML PATH ('')), 1, 1, ''  
                   ),'') AS correctedcode,  
       ISNULL(STUFF(  
                    (SELECT   
                    ',' + case when dCode2 is null then dCode1 end  
               from Q2 t2  
         where t2.Billid=t.Billid  
        FOR XML PATH ('')), 1, 1, ''  
                   ),'') AS deletedcode,  
       ISNULL(STUFF(  
                    (SELECT   
                    ',' + case when dCode1 is null then dCode2 end  
               from Q2 t3  
        where t3.Billid=t.Billid  
        FOR XML PATH ('')), 1, 1, ''  
                   ),'') AS addedcode  
     from Q2 as t  
     group by Billid, Ctextid  
     order by Billid  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    1 person found this answer helpful.