-
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 additional answer
Sort by: Most helpful
-
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
-
Analyzing the Differences entered by a coder and an auditor on a transaction

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 :sequenceNumber should be just Number ..Sorry for the confusion..
I have just edited the same in the question
Is there a way to know who (User) is a coder or an auditor?
@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)..
Sign in to comment