Filter data based on current weekday

[mohtamimi94]

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;

[Kurt_Bremser]

In other parts of your query, you use the DATEPART function on ACTIVATION_DATE, suggesting that it is in fact a datetime value; therefore, comparing it to a date value will fail.

[Tom · Accepted solution]

Looks like the issue is that the variable that has the name t1.ACTIVATION_DATE probably actually has DATETIME values (despite its name) instead of DATE values.  So you need to use the DATEPART() function if you want to compare its values to date values.

  AND datepart(t1.ACTIVATION_DATE)
      BETWEEN (CASE WHEN weekday(today()) = 1 THEN TODAY()-3
                    WHEN weekday(today()) > 1 THEN TODAY()-1
               END)
      AND today()

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
  ,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 datepart(t1.ACTIVATION_DATE)
      BETWEEN (CASE WHEN weekday(today()) = 1 THEN TODAY()-3
                    WHEN weekday(today()) > 1 THEN TODAY()-1
               END)
      AND today()
;
QUIT;

[mohtamimi94]

Solved, thank you