I have table 'tbl_1' with below columns. I need to delete data for revenue column for current month and last month every time I run the script which is daily and reload current month and last month. I have created partition on activity_dt and kpi_nm. Kpi_nm for revenue is ‘revenue’. How can I delete my partition? I have below query which does not work. Do I have to create shell and pass parameters? I would like to avoid it.
Activity_dt, Kpi_nm, Total_revenue, Gross_adds
alter table da_devstg_tbls.offline_transactions drop if exists partition ((activity_dt between ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-1) AND (CURRENT_DATE-1)) , kpi_nm = 'revenue');