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