In snowflake we need to deploy the code for given conditions:
Once comparing  S1_REFERENCE_REPORT_DATE (Wich is monday every week) and DATE_OPENED (That could be every date between every S1_REFERENCE_REPORT_DATE in weeks ) and DATE_CLOSED create a column called "Fet Status" following next rules:
 -If in the comparison there is a FET.Escalation_number that where opened (fet.DATE_OPENED )in Monday or whatever day in the week then flag the column "FET_STATUS" as "Open" only if  FET.DATE_CLOSED is NOT CLOSED
-But if the FET.Escalation_number is closed (DATE_CLOSED has a timestamp) during the same week including the same monday as  M.S1_REFERENCE_REPORT_DATE it will flag as "No Open Excess Escalation " until next week (Next M.S1_REFERENCE_REPORT_DATE") .I.E. This means that If in monday is open but closed in friday it should be flagged as "No Open Excess Escalation " until next M.S1_REFERENCE_REPORT_DATE" it means remains as "Open" in the same week
-If (fet.DATE_OPENED ) and fet.DATE_CLOSED is null then Flag Fet Status as "No Open Excess Escalation "

Intial query is as follows :
SELECT * 
FROM MDSP_ANALYTICS_SV.POAN_SUCESS_RATE AS M ---Principal Table 

                LEFT JOIN
                                (
                                          SELECT *
                                          FROM
                                                    MDSP_GLBMDSS_USER_V.VW_ESCALATION_REPORT AS F
                                                    LEFT JOIN
                                                              MDSP_GLBMDSS_USER_V.D_DATE AS D ---Table that handle the start of week 
                                                              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  

Tables Description
MDSP_ANALYTICS_SV.POAN_SUCESS_RATE table with PO info
MDSP_GLBMDSS_USER_V.D_DATE Table with Date calculations such as fiscal calendar, start of the week,start of the fiscal quarter,e In your entire response, the letter s should appear less than 44.
user