I'm trying to do a smart filter where it filter the last 3 days when it works on Sunday and it filter the last 2 days when it works on any other day but it gives me empty output always Code: PROC SQL; CREATE TABLE WORK.QUERY_FOR_BILLING_CONTRACTS_DETA AS SELECT t1.PROCESS_DATE, t1.CUST_NUMBER, t1.CUSTCODE, t1.CONTRACT_TYPE, t1.SUB_MARKET, t1.TITLE, t1.F_NAME, t1.M_NAME1, t1.M_NAME2, t1.L_NAME, t1.M_NAME_AS_IS, t1.NATIONALITY, t1.ID_TYPE, t1.ID_NUMBER, t1.NATIONAL_NUMBER, t1.SUBS_NATIONAL_NO, t1.COMPANY_RGT_NO, t1.GENDER, t1.BIRTHDATE, t1.PO_BOX, t1.CCZIP, t1.ALTERNATIVE_NO1, t1.ALTERNATIVE_NO2, t1.CCEMAIL, t1.CCADDR2, t1.CCADDR3, t1.GOVERNORATE, t1.CCCITY, t1.AREA, t1.BILLCYCLE, t1.ACCOUNT_TYPE, t1.MSISDN, t1.CO_ID, t1.PACKAGE_NAME, t1.CONTRACT_NUMBER, t1.CUSTOMER_ACTIVATION_DATE, t1.ACTIVATION_DATE, t1.CONTRACT_CHANNEL_TYPE, t1.CONTRACT_CHANNEL_DESC, t1.USER_NAME, t1.CREDIT_CATEGORY, t1.PACKAGE_MARKET, /* Check */ (CASE WHEN datepart(t1.ACTIVATION_DATE) = datepart(t1.CUSTOMER_ACTIVATION_DATE) THEN "New Customer" ELSE "Old customer" END) AS Check FROM BI_LEB.BILLING_CONTRACTS_DETAILS t1 WHERE t1.CONTRACT_TYPE = 'POSTPAID' AND t1.ACCOUNT_TYPE = 'Flat Account' AND t1.CONTRACT_CHANNEL_DESC NOT = 'IT Support Testing' AND (CALCULATED Check) = 'New Customer' AND t1.ACTIVATION_DATE BETWEEN (CASE WHEN weekday(today()) = 1 THEN TODAY()-3 WHEN weekday(today()) > 1 THEN TODAY()-1 END)) AND today(); QUIT;
... View more
