Har bir tranzaksiya uchun hafta oxiri va bayramlarni hisobga olgan holda maksimal kurs sanasi bilan so'rovni qanday amalga oshirish mumkin?

Har bir tranzaksiya uchun, hafta oxiri va bayramlarni hisobga olgan holda, maksimal sanaga ega kursni tanlash uchun:

1. Valyuta kurslari uchun jadval bo'lishi kerak, unda kursning amal qilish sanalari ko'rsatilgan.
2. Kalendardan iborat jadval bo'lishi kerak, unda hafta oxiri va bayramlar belgilangan.

So'rovning fikri — har bir tranzaksiya uchun, tranzaksiya sanasidan oshmaydigan va ish kuni bo'lgan maksimal kurs sanasini topish.

SQL misoli (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
;

Shu tarzda, har bir tranzaksiya uchun, tranzaksiya sanasidan keyingi bo'lmagan va ish kuni bo'lgan maksimal kurs sanasi tanlanadi.