SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/*
|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|
*/
CREATE TABLE fx
(
  from_currency varchar(3)
, month int
, year int
, exchange_rate decimal(12,4)
);
INSERT INTO fx
VALUES
  ('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.8050)
, ('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);
12 rows affected

SELECT from_currency
  , DATEFROMPARTS(year, month, 1) AS rate_date
  , exchange_rate
FROM fx;


from_currency
rate_date
exchange_rate


GBP
2023-01-01
0.7952

GBP
2023-02-01
0.7738

GBP
2023-03-01
0.7934

GBP
2024-01-01
0.8031

GBP
2024-02-01
0.7938

GBP
2024-03-01
0.8050

CNY
2023-01-01
0.7952

CNY
2023-02-01
0.7738

CNY
2023-03-01
0.7934

CNY
2023-04-01
0.8031

CNY
2023-05-01
0.7938

CNY
2023-06-01
0.8031


/* using a new sql server 2022 function */
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));


from_currency
qtr
avg_exchange_rate


CNY
2023-01-01
0.787466

GBP
2023-01-01
0.787466

CNY
2023-04-01
0.800000

GBP
2024-01-01
0.800633


/* before 2022 */
SELECT from_currency
  , CONVERT(date, DATEADD(quarter, DATEDIFF(quarter, 0, DATEFROMPARTS(year, month, 1)), 0)) AS qtr
  , AVG(exchange_rate) AS avg_exchange_rate
FROM fx
GROUP BY from_currency
  , DATEADD(quarter, DATEDIFF(quarter, 0, DATEFROMPARTS(year, month, 1)), 0);


from_currency
qtr
avg_exchange_rate


CNY
2023-01-01
0.787466

GBP
2023-01-01
0.787466

CNY
2023-04-01
0.800000

GBP
2024-01-01
0.800633

from_currency	rate_date	exchange_rate
GBP	2023-01-01	0.7952
GBP	2023-02-01	0.7738
GBP	2023-03-01	0.7934
GBP	2024-01-01	0.8031
GBP	2024-02-01	0.7938
GBP	2024-03-01	0.8050
CNY	2023-01-01	0.7952
CNY	2023-02-01	0.7738
CNY	2023-03-01	0.7934
CNY	2023-04-01	0.8031
CNY	2023-05-01	0.7938
CNY	2023-06-01	0.8031

from_currency	qtr	avg_exchange_rate
CNY	2023-01-01	0.787466
GBP	2023-01-01	0.787466
CNY	2023-04-01	0.800000
GBP	2024-01-01	0.800633