根据指定日期查询对应生效汇率的SQL语句开发需求

问题背景 #

我有一张汇率表（CurrencyRatetable），仅在汇率更新时新增记录，表中仅存储新汇率的生效日期（effective_from）。系统规则为：任意日期若处于某一汇率的生效周期内，则选取该对应的汇率。需要编写SQL查询，根据输入的任意日期获取对应的生效汇率。

原尝试代码（存在逻辑问题） #

WITH ListDates(AllDates) AS
(    SELECT cast('2015-11-01' as date) AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < getdate())
SELECT  ld.AllDates,cr.effective_from,cr.rate_against_base
FROM ListDates ld
left join CurrencyRatetable cr on cr.effective_from between cr.effective_from and ld.alldates
option (maxrecursion 0)

原代码问题分析 #

关联条件cr.effective_from between cr.effective_from and ld.alldates逻辑无效，该条件等价于cr.effective_from <= ld.AllDates，会将所有小于等于目标日期的汇率记录都与该日期关联，导致一条日期对应多条汇率，无法得到正确的生效汇率。

解决方案 #

1. 单个日期查询 #

若只需查询指定日期对应的汇率，可通过子查询找到最大的且不超过目标日期的生效日期，再匹配对应汇率：

-- 替换'2024-05-20'为目标日期
SELECT cr.effective_from, cr.rate_against_base
FROM CurrencyRatetable cr
WHERE cr.effective_from = (
    SELECT MAX(effective_from)
    FROM CurrencyRatetable
    WHERE effective_from <= '2024-05-20'
)

2. 批量日期范围查询 #

若需生成指定日期范围内（如2015-11-01至当前日期）每天对应的汇率，使用OUTER APPLY替代原LEFT JOIN，对每个日期精准匹配最新生效汇率：

WITH ListDates(AllDates) AS (
    SELECT CAST('2015-11-01' AS DATE) AS AllDates
    UNION ALL
    SELECT DATEADD(DAY, 1, AllDates)
    FROM ListDates 
    WHERE AllDates < GETDATE()
)
SELECT 
    ld.AllDates,
    cr.effective_from,
    cr.rate_against_base
FROM ListDates ld
OUTER APPLY (
    -- 找到当前日期之前最新生效的汇率
    SELECT TOP 1 effective_from, rate_against_base
    FROM CurrencyRatetable
    WHERE effective_from <= ld.AllDates
    ORDER BY effective_from DESC
) cr
OPTION (MAXRECURSION 0)

内容的提问来源于stack exchange，提问作者Michael Charinda