基于参数日期获取事务数据集最新记录的SQL UDF实现

数据集 #

需求说明 #

• 创建接收日期参数的SQL用户定义函数（UDF）
• 返回有效期覆盖传入日期的最新记录
• 若单日存在多条符合条件的记录，返回时间戳最新的那条（即REC_START_DATE最晚的记录）
• 示例：传入'2015-08-01'时，应返回RowID为8的记录，该记录的有效期（2015-07-23至2016-05-22）覆盖了传入日期，且是对应时间段的最新数据

初始代码问题 #

初始实现的WHERE条件存在逻辑错误：

1. REC_END_DATE > @paramDate 无法准确判断日期是否在有效期内，正确逻辑应为传入日期处于REC_START_DATE和REC_END_DATE之间（包含边界）
2. year(REC_END_DATE) = year(@paramDate) 会错误过滤跨年度的有效记录（比如RowID8的REC_END_DATE为2016年，传入2015年日期时会被排除）

修正后的UDF实现 #

CREATE OR REPLACE FUNCTION getLastRec (
    @paramDate date
)
RETURNS TABLE AS
RETURN
    SELECT TOP 1 * 
    FROM MyTable
    -- 判断传入日期是否在记录的有效期范围内
    WHERE REC_START_DATE <= @paramDate 
      AND REC_END_DATE >= @paramDate
    -- 按记录起始时间倒序，确保取到最新的有效记录；若RowID为自增序列，也可替换为ORDER BY RowID DESC
    ORDER BY REC_START_DATE DESC

调用示例 #

SELECT * FROM getLastRec('2015-08-01')

调用后将返回RowID为8的记录，符合需求。

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