Search code examples
sqlsnowflake-cloud-data-platformiics

Query to delete monthly data from a table where load is done weekly

I have a table where data is loaded on weekly basis but I have to delete it on monthly basis. Scenario is like when the data is loaded it should delete all the data from the table for the present month. Table fields are like: Data_id - integer Data_week - Date/time - DD/MM/YYYY

Query written - Objective: To delete the entire 1 months data from the latest recent load. All Previous data to be retained.

DELETE FROM MHCD_AGG_CUST_ORG_SLS_WIDE_WK
WHERE DATA_WEEK >
  (SELECT DISTINCT(DATA_WEEK)
   FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK
   WHERE EXTRACT(MONTH FROM DATA_WEEK) !=
           EXTRACT(MONTH FROM (SELECT MAX(DATA_WEEK) FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK))
     AND EXTRACT(YEAR FROM DATA_WEEK) <=
           EXTRACT(YEAR FROM (SELECT MAX(DATA_WEEK) FROM ADS.MHCD_AGG_CUST_ORG_SLS_WIDE_WK))
   ORDER BY DATA_WEEK DESC);

Kindly help to correct the query as I am stuck to phrase the correct one.

Solution

  • Why not just do this:

    WHERE to_char(data_week, 'yyyy-mm') = to_char(current_date, 'yyyy-mm')

    Update following latest comment

    WHERE to_char(data_week, 'yyyy-mm') = 
     (SELECT to_char(MAX(data_week), 'yyyy-mm') 
      FROM MHCD_AGG_CUST_ORG_SLS_WIDE_WK
     )