With Tb1 as
(select Date,Particulars,BillAmount,0'PaidAmount' from tblBill
union
select Date,Particulars,0'BillAmount',PaidAmount from tblPayment
)
SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
INNER JOIN
Tb1 as T2
ON T1.[date] >= T2.[date]
Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
Order by [Date]
What I have tried:
DECLARE @JsonParsing NVARCHAR(MAX) = N'{"HD" :["CombinedMetadata:energy","CombinedMetadata:enron","energy","enron","information","time"],"HF":[2, 2, 3, 35, 2, 8],"REx":[]}'
SELECT S.DATA,SUM(t.Frequency) AS FREQUENCY
FROM (SELECT RIGHT(q.VALUE, LEN(q.VALUE) - CHARINDEX(':', q.VALUE)) AS DATA,
ROW_NUMBER() OVER(ORDER BY(SELECT 0)) AS Row_nu
FROM OPENJSON(@JsonParsing)
WITH (HD NVARCHAR(MAX) AS JSON) AS p
CROSS APPLY OPENJSON(p.HD) AS q) s,
(SELECT CONVERT(INT, b.VALUE) AS Frequency,
ROW_NUMBER() OVER(ORDER BY(SELECT 0)) AS Row_nu
FROM OPENJSON(@JsonParsing)
WITH (HF NVARCHAR(MAX) AS JSON) AS a
CROSS APPLY OPENJSON(a.HF) AS b) t
WHERE t.Row_nu = s.Row_nu
GROUP BY s.DATA