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

Question

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

Hellothere8028 821

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

Guoxiong 8,206 Reputation points

2020-11-25T15:55:05.96+00:00

What are the different between the Number and SequenceNumber in the column Info?
Hellothere8028 821 Reputation points

2020-11-25T16:21:06.807+00:00

@Guoxiong :sequenceNumber should be just Number ..Sorry for the confusion..
Hellothere8028 821 Reputation points

2020-11-25T16:24:13.273+00:00

I have just edited the same in the question
Guoxiong 8,206 Reputation points

2020-11-25T18:52:49.873+00:00

Is there a way to know who (User) is a coder or an auditor?
Hellothere8028 821 Reputation points

2020-11-26T09:53:55.967+00:00

@Guoxiong : This can be determined using created for the (Billid and ctextid) combination..Arranged by ascending order of created is the coder followed by auditor (So auditor does it after coder)..

Accepted answer

1 additional answer

Your answer

Guoxiong 8,206 Reputation points

2020-11-25T15:55:05.96+00:00

What are the different between the Number and SequenceNumber in the column Info?
Hellothere8028 821 Reputation points

2020-11-25T16:21:06.807+00:00

@Guoxiong :sequenceNumber should be just Number ..Sorry for the confusion..
Hellothere8028 821 Reputation points

2020-11-25T16:24:13.273+00:00

I have just edited the same in the question
Guoxiong 8,206 Reputation points

2020-11-25T18:52:49.873+00:00

Is there a way to know who (User) is a coder or an auditor?
Hellothere8028 821 Reputation points

2020-11-26T09:53:55.967+00:00

@Guoxiong : This can be determined using created for the (Billid and ctextid) combination..Arranged by ascending order of created is the coder followed by auditor (So auditor does it after coder)..

Answer 1

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

Hellothere8028 821 Reputation points

2020-11-26T11:47:58.417+00:00

Thanks Viorel-1 !..Really appreciate your help!!..This is Awesome!!..

Answer 2

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

Hellothere8028 821 Reputation points

2020-11-26T11:49:04.177+00:00

@MelissaMa-msft : Thank you for being so much helpful!..

Share via

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

1 additional answer

Your answer