Hi All,
Hope you are doing well!..I have a billing file data in which I am trying to compare JSON data for each pair of Billid and Ctextid ..Each pair of Billid and Ctextid have two rows of JSON data..I am trying to check if the two rows of JSON are the same or whether there are any changes made... Can you please help me here!..Please find below the sample Input data and Output data..
Input table
create table ##input1
(Billid int,
Ctextid int,
info JSON,
user varchar(30)
)
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'),"
"('2132','91156','[
{
""description"": ""fabula "",
""dCode"": ""8901"",
""CodeId"": ""90001"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""EXper "",
""dCode"": ""9034"",
""CodeId"": ""88343"",
""messages"": [
""""
],
""Number"": 2
}
]','all1'),"
"('5678','99344','[
{
""description"": ""TORYA "",
""dCode"": ""99002"",
""CodeId"": ""988332"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""triact "",
""dCode"": ""90774"",
""CodeId"": ""7800034"",
""messages"": [
""""
],
""Number"": 2
}
]','jk1'),"
"('5678','99344','[
{
""description"": ""TORYA "",
""dCode"": ""99002"",
""CodeId"": ""988332"",
""messages"": [
""""
],
""Number"": 1
},
{
""description"": ""triact "",
""dCode"": ""90974"",
""CodeId"": ""78034"",
""messages"": [
""""
],
""Number"": 2
}
]','jk2'),"
Ouput Table
create table #output
(Billid int,
Ctextid int,
Infochange varchar(20)
)
Insert into #output values
('2132','91156','No'),
('5678','99344','Yes')
Thanks,
Arun