Accepted answer
-
Viorel 88,656 Reputation points2020-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 BillidResults:
Billid Ctextid codername correctedcode deletedcode addedcode 2132 91156 amt1 887756 5678 99344 jk1 90774 7789 60045 ec1 Z003345, H678 U8923, Y98620 J18907-
Hellothere8028 761 Reputation points2020-11-26T11:47:58.417+00:00 Thanks Viorel-1 !..Really appreciate your help!!..This is Awesome!!..
Sign in to comment1 additional answer
Sort by: Most helpful
-
MelissaMa-MSFT 24,131 Reputation points2020-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 BillidBest 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 tableSign in to answer-
Hellothere8028 761 Reputation points
2020-11-26T11:49:04.177+00:00 @MelissaMa-MSFT : Thank you for being so much helpful!..
Sign in to comment -
-
@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