我一直在寻找这个问题的答案,似乎有很多不同的“解决方案”,没有什么是我正在寻找的。我有下面的查询,运行很好,但我需要能够修改“2021-08-15”格式的日期,以便根据列选择上一个星期日或上一个星期日。我需要能够只运行查询并获得动态结果,而不是手动更改每周的日期。例如,使用当前日期是否可以做到这一点?我的所有尝试都没有产生任何结果:
SELECT
RMD.ISSUE_ID,
MAX(CASE WHEN AS_OF_DATE = '2021-08-15' THEN CURRENT.ISSUE_TITLE END) AS CURR_ISSUE_TITLE,
MAX(CASE WHEN AS_OF_DATE = '2021-08-15' THEN CURRENT.MEMBER_IMPACT END) AS CURR_MEMBER_IMPACT,
MAX(CASE WHEN AS_OF_DATE = '2021-08-08' THEN CURRENT.MEMBER_IMPACT END) AS PREV_MEMBER_IMPACT,
MAX(CASE WHEN AS_OF_DATE = '2021-08-15' THEN CURRENT.ISM_STATUS END) AS CURR_ISM_STATUS,
MAX(CASE WHEN AS_OF_DATE = '2021-08-15' THEN CURRENT.ISSUE_OWNER_ORG END) AS CURR_ISSUE_OWNER_ORG,
MAX(CASE WHEN AS_OF_DATE = '2021-08-15' THEN CURRENT.ISSUE_OWNER END) AS CURR_ISSUE_OWNER,
MAX(CASE WHEN AS_OF_DATE = '2021-08-15' 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 ('2021-08-08', '2021-08-15')
GROUP BY RMD.ISSUE_ID
HAVING MAX(CASE WHEN AS_OF_DATE = '2021-08-08' THEN CURRENT.MEMBER_IMPACT END) <> MAX(CASE WHEN AS_OF_DATE = '2021-08-15' THEN CURRENT.MEMBER_IMPACT END) ;
正如你所看到的,我有很多依赖于日期,但他们都是最近或前一个星期天。任何帮助或方向将是伟大的,谢谢你的时间。
1条答案
按热度按时间zrfyljdw1#
试试看:
请参阅Date operations and durations和DAYOFWEEK_ISO scalar function。