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)..The comparison can be made between the set of JSON's (coder and auditor) by using the number or the description, that is the sequences to be compared can be found by matching on the number or the description(for a pair of billid and ctextid).. ..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')