DB2如何动态获取最近两个周日日期替换SQL固定日期

核心日期计算逻辑 #

DB2内置的DAYOFWEEK函数返回值规则为：1=周日、2=周一...7=周六，基于这个规则可以动态计算所需的两个周日日期：

• 最近周日（若当前日期为周日则直接取当天）：CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS
• 上上周日：在最近周日的基础上再减7天即可，即CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS - 7 DAYS

如果你的AS_OF_DATE字段是字符串类型，用格式参数10转换为YYYY-MM-DD格式的字符串即可：

• 最近周日字符串：CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10)
• 上上周日字符串：CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS - 7 DAYS, 10)

修改后的完整查询 #

SELECT 
    RMD.ISSUE_ID,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.ISSUE_TITLE END) AS CURR_ISSUE_TITLE,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.MEMBER_IMPACT END) AS CURR_MEMBER_IMPACT,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS -7 DAYS, 10) THEN CURRENT.MEMBER_IMPACT END) AS PREV_MEMBER_IMPACT,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.ISM_STATUS END) AS CURR_ISM_STATUS,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.ISSUE_OWNER_ORG END) AS CURR_ISSUE_OWNER_ORG,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.ISSUE_OWNER END) AS CURR_ISSUE_OWNER,
    MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.ISSUE_APPROVER END) AS CURR_ISSUE_APPROVER
FROM LOD.ISM_ISSUE_SUMMARY_HIST_WKY CURRENT INNER JOIN
     LOD.RMD_ISS_REMED_SUMMARY RMD
     ON CURRENT.ISSUE_ID = RMD.ISSUE_ID
WHERE AS_OF_DATE IN (
    CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10),
    CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS -7 DAYS, 10)
) 
GROUP BY RMD.ISSUE_ID
HAVING MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS -7 DAYS, 10) THEN CURRENT.MEMBER_IMPACT END) <> MAX(CASE WHEN AS_OF_DATE = CHAR(CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS, 10) THEN CURRENT.MEMBER_IMPACT END) ;

业务适配说明 #

如果你的业务规则要求即使当天是周日，也要取上周日作为最近周日，只需要把所有日期计算逻辑里的(DAYOFWEEK(CURRENT DATE) - 1)改成DAYOFWEEK(CURRENT DATE)即可，调整后会统一往前推到上一个完整的周日，不会包含当天数据。

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