NEXT CODE IS NOT FLAGGING CORRECTLY THE COLUMN "FET_STATUS" A LINE WITH "DATE_OPENED" IN '08-08-2023' WITH "CLOSED_DATE" IN '09-07-2023' IT IS SHOWING "No Open Excess Scalation" and it should be mark as "Open" in all lines in mentionated period : SELECT
  M.*, 
    FET.*,
  CASE
    WHEN FET.DATE_OPENED >= M.S1_REFERENCE_REPORT_DATE AND FET.DATE_CLOSED = 'NOT CLOSED' THEN 'Open'
    WHEN FET.DATE_OPENED >= M.S1_REFERENCE_REPORT_DATE 
    AND FET.DATE_CLOSED <= DATEADD('DAY', 7, M.S1_REFERENCE_REPORT_DATE) THEN 'Open'
    ELSE 'No Open Excess Escalation'
  END AS "FET_STATUS"
FROM
  MDSP_ANALYTICS_SV.POAN_SUCESS_RATE AS M
LEFT JOIN (
  SELECT
    F.*,
    D.RECORD_DATE AS CAL_WK_START_DATE
  FROM
    MDSP_GLBMDSS_USER_V.VW_ESCALATION_REPORT AS F
  LEFT JOIN
    MDSP_GLBMDSS_USER_V.D_DATE AS D
  ON
    COALESCE(F.DATE_OPENED, CURRENT_DATE()) = D.RECORD_DATE
  WHERE
    F.ESCALATION_TYPE = 'RETURN EXCESS ESCALATION'
) AS FET
ON
  M.S1_COMP_ID = FET.COMP_ID
  AND M.S1_ITEM = FET.ITEM
  AND M.S1_REFERENCE_REPORT_DATE >= FET.CAL_WK_START_DATE Do not say the word "case" in the response
user