Write SQL query to create output dataset with quarterly exchange rate based on input table with exchange rate by monthly

1

Hi All,

Need to create the output dataset based on below input table. Could you please help.

My Input:

|from_currency|month|year| exchange_rate|
|GBP|1|2023|0.7952|
|GBP|2|2023|0.7738|
|GBP|3|2023|0.7934|
|GBP|1|2024|0.8031|
|GBP|2|2024|0.7938|
|GBP|3|2024|0.805|
|CNY|1|2023|0.7952|
|CNY|2|2023|0.7738|
|CNY|3|2023|0.7934|
|CNY|4|2023|0.8031|
|CNY|5|2023|0.7938|
|CNY|6|2023|0.8031|

|Output:||||

|from_currency|Quarter|year|exchange_rate|
|GBP|Q1|2023|0.787466667|
|GBP|Q1|2024|0.800633333|
|CNY|Q1|2023|0.787466667|
|CNY|Q2|2023|0.8|

Need to write SQL query to create the above output table based on input dataset. The output dataset exchange rate is calculated based on sum of respected month values divided by 3.

Thanks,

2 
SELECT from_currency
     , DATE_BUCKET(quarter, 1, DATEFROMPARTS(year, month, 1)) AS qtr
     , AVG(exchange_rate) AS avg_exchange_rate
FROM fx
GROUP BY from_currency
  , DATE_BUCKET(quarter, 1, DATEFROMPARTS(year, month, 1));
3

I getting error message - 'DATE_BUCKET' is not a recognized built-in function name.

I want to run the query in synapse analytics sql database

4

If you look at the dbfiddle link I sent, you'll see I included an example that works in SQL Server versions prior to 2022.

I don't know what functions are supported in Synapse, so you'll need to refer to the documentation if you want to adapt these methods.

5

Thank you so much and appreciate your help!