Kuidas rakendada päringut maksimaalse kursi kuupäevaga iga tehingu jaoks, arvestades nädalavahetusi ja pühade päevi?

To implement the query that, for each transaction, selects the rate with the maximum date, considering weekends and holidays, you need:

1. A table with exchange rates, indicating the dates when the rate was valid.
2. A calendar table marking weekends and holidays.

The idea of the query is — for each transaction — to find the maximum rate date that does not exceed the transaction date and is a working day.

Example in SQL (PostgreSQL):

WITH working_days AS (
  SELECT date
  FROM calendar
  WHERE is_holiday = FALSE AND is_weekend = FALSE
),
max_course_dates AS (
  SELECT t.transaction_id, MAX(c.date) AS max_course_date
  FROM transactions t
  JOIN courses c ON c.date <= t.transaction_date
  JOIN working_days wd ON c.date = wd.date
  GROUP BY t.transaction_id
)
SELECT t.*, c.rate
FROM transactions t
JOIN max_course_dates mcd ON t.transaction_id = mcd.transaction_id
JOIN courses c ON c.date = mcd.max_course_date
  AND c.currency = t.currency
;

This way, for each transaction, the course with the maximum date that is not later than the transaction date and is a working day (not weekend, not holiday) is selected.