Click here to Skip to main content
15,890,982 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 8-Apr-19 3:00am
v4
Comments
Karthik_Mahalingam 1-Mar-19 1:20am    
which version of sql server you are using?
Member 14156756 1-Mar-19 1:27am    
ssms 2016
#realJSOP 1-Mar-19 15:01pm    
ssms is not sql server. It's a gui tool that lets you perform queries, etc.
Ram Nunna 1-Mar-19 1:40am    
This link might help u.
https://www.codeproject.com/Articles/1087995/Inserting-JSON-Text-into-SQL-Server-Table

1 solution

 
Share this answer
 
Comments
Maciej Los 4-Mar-19 4:41am    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900