如何关联Calendar表与CurrencyRate表查询缺失汇率的日期？

核心查询（全局汇率场景） #

如果你的CurrencyRate表仅存储全局汇率（无多货币对区分），可以用左连接筛选出未被任何FromDate-ToDate区间覆盖的日期：

SELECT CAST(c.CalendarDate AS DATE) AS MissingDate
FROM [Calendar] c
LEFT JOIN CurrencyRate cr 
  ON CAST(c.CalendarDate AS DATE) BETWEEN cr.FromDate AND cr.ToDate
WHERE cr.FromDate IS NULL

多货币对场景 #

如果CurrencyRate包含FromCurrency和ToCurrency字段（区分不同货币对的汇率），需要先生成所有日期与货币对的组合，再筛选每个货币对的缺失日期：

WITH DateCurrencyPairs AS (
    -- 生成所有日期 × 所有货币对的组合
    SELECT 
        CAST(c.CalendarDate AS DATE) AS CalendarDate,
        crp.FromCurrency,
        crp.ToCurrency
    FROM [Calendar] c
    CROSS JOIN (SELECT DISTINCT FromCurrency, ToCurrency FROM CurrencyRate) crp
)
SELECT 
    dcp.CalendarDate AS MissingDate,
    dcp.FromCurrency,
    dcp.ToCurrency
FROM DateCurrencyPairs dcp
LEFT JOIN CurrencyRate cr 
  ON dcp.FromCurrency = cr.FromCurrency
  AND dcp.ToCurrency = cr.ToCurrency
  AND dcp.CalendarDate BETWEEN cr.FromDate AND cr.ToDate
WHERE cr.FromDate IS NULL

关键注意事项 #

• 日期格式统一：如果Calendar表的CalendarDate是datetime类型（带时间），务必用CAST(xxx AS DATE)转换为纯日期，避免时间部分导致匹配失败。
• 过滤未来日期：如果只需要排查到当前日期的缺失数据，可在查询末尾添加过滤条件：

  AND CAST(c.CalendarDate AS DATE) <= CAST(GETDATE() AS DATE)
  

内容的提问来源于stack exchange，提问作者Umesh Deshmukh