Comparing JSON data in two rows for every set of ID

Question

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

Answer 1

;WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Billid, Ctextid ORDER BY UserName) AS RN   
    FROM #input1
)

SELECT c1.Billid, c1.Ctextid, CASE WHEN c1.Info = c2.Info THEN 'No' ELSE 'Yes' END Infochange
FROM CTE AS c1
INNER JOIN CTE AS c2 ON c2.Billid = c1.Billid AND c2.Ctextid = c1.Ctextid
WHERE c1.RN = 1 AND c2.RN = 2;

Answer 2

Hi @Hellothere8028 ,

Thank you so much for posting here.

You could also try with rank() which is similar with rownumber().

select a.Billid,a.Ctextid,case when a.info=b.info then 'no' else 'yes' end Infochange   
from (  
 select Billid,Ctextid,info,rank() over (PARTITION BY Billid, Ctextid ORDER BY [User]) as row_num  
 FROM ##input1  
) AS a  
inner join (  
 select Billid,Ctextid,info,rank() over (PARTITION BY Billid, Ctextid ORDER BY [User]) as row_num  
 FROM ##input1  
) AS b on a.Billid=b.Billid and a.Ctextid=b.Ctextid and a.row_num = b.row_num +1  

In addition, you could use lag() or lead() function as below:

;with cte as (  
select Billid, Ctextid,info,  
LAG(info) OVER (PARTITION BY Billid, Ctextid ORDER BY [User]) laginfo  
from ##input1)  
select Billid, Ctextid,case when info=laginfo then 'no' else 'yes' end Infochange   
from  cte where laginfo is not null  

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 October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet