SQL How to nest multiple Case When Queries and add a condition-CodePudding

I am trying to divide customers (contact_key) column who shopped in 2021 (A.TXN_MTH) into new and 'returning' with returning meaning that they had not shopped in the last 12 months (YYYYMM in X.Fiscal_mth_idnt column).

I am using CASE WHEN A.TXN_MTH = MIN(X.FISCAL_MTH_IDNT) THEN 'NEW' which is correct. The next case when should be when the max month before X.TXN_MTH is 12 or more months previous. I have added the 12 months part in the Where statement. Should I be nesting 3 CASE WHEN'S instead of WHERE?

SELECT 
                    T.CONTACT_KEY
                ,   A.TXN_MTH
                ,   CASE    WHEN A.TXN_MTH = MIN(X.FISCAL_MTH_IDNT) THEN 'NEW'
                            WHEN (MAX(CASE WHEN X.FISCAL_MTH_IDNT < A.TXN_MTH THEN X.FISCAL_MTH_IDNT ELSE NULL END)) THEN 'RETURNING'
                            END AS CUST_TYPE                                
            FROM B_TRANSACTION T
            INNER JOIN B_TIME X
                ON T.TRANSACTION_DT_KEY = X.DATE_KEY
            INNER JOIN A
                ON A.CONTACT_KEY = T.CONTACT_KEY AND A.BU_KEY = T.BU_KEY 
            WHERE (MAX(CASE WHEN X.FISCAL_MTH_IDNT < A.TXN_MTH THEN X.FISCAL_MTH_IDNT ELSE NULL END)) < A.TXN_MTH - (date_format(add_months(concat_ws('-',substr(yearmonth,1,4),substr(yearmonth,5,2),'01'),-12),'yyyyMM')
            GROUP BY
                    T.CONTACT_KEY
                ,   TXN_MTH;

CodePudding user response：

Move you where statement to your case when

CodePudding user response：

You have not described your tables, so assuming fiscal_mth_idnt is a DATE column then you can use the LAG analytic function to find the previous row's value:

SELECT contact_key,
       txn_mth,
       CASE
       WHEN prev_fiscal_mth_idnt IS NULL
       THEN 'NEW'
       WHEN ADD_MONTHS(prev_fiscal_mth_idnt, 12) < fiscal_mth_idnt
       THEN 'RETURNING'
       ELSE 'CURRENT'
       END AS cust_type
FROM   (
  SELECT T.CONTACT_KEY,
         A.TXN_MTH,
         yearmonth,
         X.FISCAL_MTH_IDNT,
         LAG(X.FISCAL_MTH_IDNT) OVER (
           PARTITION BY T.CONTACT_KEY
           ORDER BY X.FISCAL_MTH_IDNT
         ) AS prev_fiscal_mth_idnt
  FROM   B_TRANSACTION T
         INNER JOIN B_TIME X
         ON T.TRANSACTION_DT_KEY = X.DATE_KEY
         INNER JOIN A
         ON A.CONTACT_KEY = T.CONTACT_KEY AND A.BU_KEY = T.BU_KEY
)
WHERE  yearmonth LIKE '2021%';