My data are of payments, amount, currency and a JSON object with the exchange rates for that particular date. This JSON contains ALL currencies and all exchange rates.

How do I select just the correct rate in each object for each row? The final goal is to have one column called "Amount in SEK".

I want a result that looks something like this:

CodePudding user response：

If you use SQL Server 2016 , you may parse the JSON data with OPENJSON(). The following statement is a possible solution to your problem (without rounding):

Sample data:

SELECT *
INTO Data
FROM (VALUES
   (666.00, 'USD', '[{"BaseCurrency":"SEK","ExchangeCurrency":"GBP","Rate":0.07848,"Date":"2018-10-21"},{"BaseCurrency":"SEK","ExchangeCurrency":"USD","Rate":0.106593,"Date":"2018-10-21"}]'),
   (123.00, 'GBP', '[{"BaseCurrency":"SEK","ExchangeCurrency":"GBP","Rate":0.08239,"Date":"2020-03-10"},{"BaseCurrency":"SEK","ExchangeCurrency":"USD","Rate":0.117549,"Date":"2020-03-10"}]')
) v ([Amount], [Currency], [Exchange rates])

Statment:

SELECT 
   d.[Amount], d.[Currency], d.[Exchange rates], 
   j.[Rate], 
   CASE WHEN j.[Rate] > 0.0 THEN d.[Amount] / j.[Rate] END AS [Amount in SEK]
FROM Data d
OUTER APPLY OPENJSON(d.[Exchange rates]) WITH (
   ExchangeCurrency varchar(3) '$.ExchangeCurrency',
   Rate numeric(20, 5) '$.Rate'
) j 
WHERE d.Currency = j.ExchangeCurrency

Result:

CodePudding user response：

Thanks, That seems to be what I'm looking for and I've done a bit of research and tried on my own code but it doesn't seem to work. I've also double-checked the server version. I'm running SQL Server 13.0.6300.2 which Google tells me is 2016.

I took my data and loaded it into a sample temp table.

SELECT top 100
s.STM_AMOUNT [Amount],
d.DIC_CODE [Currency],
REPLACE([CRR_CURRENCIES],'''','"') as [Exchange rates]
into #data
FROM database.SETTLEMENTS s
left join database.CURRENCIES c on c.crr_id = s.stm_crr_id 
left join database.DICTIONARIES d on d.dic_id = s.stm_cur_dic_id

Result

select * from #data

I then do the code you suggested:

Select 
d.[Amount], d.[Currency], d.[Exchange rates], 
j.[Rate],
CASE WHEN j.[Rate] > 0.0 THEN d.[Amount] / j.[Rate] END AS [Amount in SEK]
from #data d

OUTER APPLY OPENJSON(d.[Exchange rates])
WITH(
ExchangeCurrency varchar(3) '$.ExchangeCurrency',
Rate numeric(20, 5) '$.Rate'
) j 
WHERE d.Currency = j.ExchangeCurrency 

I have also tried "CROSS APPLY" but I get this error code: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

So I tried to add a semicolon ( ; ) in front of WITH but then everything after the colon gets error.