SQL查询中未来生效日期记录的逻辑调整需求

现有查询会在当前日期返回未来生效的职级步骤记录（例如：职级步骤生效日期为2023-12-01，更新日期为2023-11-16，当前执行查询时就会返回该记录），不符合“生效日期当天或之后才返回”的需求。以下是调整后的查询语句：

select * from 
(
select grade_step_name,
       grade_code,
       grade_sequence,
       Max(last_update_date) OVER( partition BY GRADE_STEP_ID) max_update_date,
       grade_effective_start_date
from PER_GRADE_STEPS_F_VL
where :effectivedate between grade_effective_start_date and grade_effective_end_date
) t
 where :attribute = 'N'
or (
  :attribute='Y'
  and max_update_date >=
    (
      SELECT Max(processstart)
      FROM   fusion.ess_request_history erh
      JOIN   fusion.ess_request_property erp
        ON  erh.requestid = erp.requestid
      WHERE  state = 12
        AND erp.NAME = 'reportID'
    )
  -- 新增条件：确保当前查询执行日期已达到职级步骤生效日期
  AND grade_effective_start_date <= SYSDATE
)

修改说明 #

1. 新增生效日期校验：在外层判断逻辑中加入AND grade_effective_start_date <= SYSDATE，确保只有当职级步骤的生效日期不晚于当前查询执行日期时，才会在attribute='Y'的场景下返回该记录，彻底避免提前返回未来生效的记录。
2. 优化JOIN写法：将原查询中的隐式连接改为显式JOIN，提升SQL的可读性和维护性。
3. 保留原有核心逻辑：继续保留max_update_date >= 报表最后执行日期的判断，确保只提取自上次报表执行后更新过的职级步骤数据。

如果你的业务逻辑是以:effectivedate参数作为生效判断基准（而非系统当前日期），可以将SYSDATE替换为:effectivedate，不过原查询的子查询已经通过:effectivedate between grade_effective_start_date and grade_effective_end_date过滤了生效范围，这种情况下该条件属于双重验证，可根据实际需求选择是否保留。

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