时间戳

首页 > 解决方案 > Oracle/SQL 查找日期或最接近指定日期的日期

sql - Oracle/SQL 查找日期或最接近指定日期的日期

问题描述

我正在尝试在 oracle 中运行一个创建值的查询。基础表并不总是包含特定日期的匹配项。所以我必须找到指定日期的记录，或者找到指定日期下方最接近的日期。

SUM(CASE 
            WHEN S.VAL_SECURITY NOT IN ('GBP')
                AND E.SECURITY_ID NOT IN ('GBP')
                THEN (
                        (((amount) * - 1) / nvl(s.minor_per_unit, 1)) * (
                            SELECT EXCHANGE_RATE
                            FROM ISET.PREVIOUS_PRICES
                            WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
                                AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
                            )
                        ) * (
                        SELECT EXCHANGE_RATE
                        FROM ISET.PREVIOUS_PRICES
                        WHERE SECURITY_ID = S.VAL_SECURITY
                            AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
                        )
            WHEN S.VAL_SECURITY IN ('GBP')
                AND E.SECURITY_ID NOT IN ('GBP')
                THEN (
                        (((amount) * - 1) / nvl(s.minor_per_unit, 1)) * (
                            SELECT EXCHANGE_RATE
                            FROM ISET.PREVIOUS_PRICES
                            WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
                                AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')
                            )
                        )
            ELSE ((amount) * - 1) / nvl(s.minor_per_unit, 1)
END) GBP_VALUE

有问题的代码是

E.SECURITY_ID AND VAL_DATE = TO_DATE('05/08/2019', 'DD/MM/YYYY')

因此，假设它没有找到指定日期的记录，那么它需要找到最接近但低于指定日期的最大日期记录。

任何帮助将不胜感激。

谢谢

标签： sqloracle

解决方案

使用更多嵌入式 SQL 级别

SELECT EXCHANGE_RATE
  FROM ISET.PREVIOUS_PRICES
 WHERE ISET.PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID
       AND VAL_DATE = (
       SELECT MAX(val_date)
         FROM iset.previous_prices
        WHERE val_date <= TO_DATE ( '05/08/2019', 'DD/MM/YYYY' ) 
         and PREVIOUS_PRICES.SECURITY_ID = E.SECURITY_ID);

推荐阅读