Limiting records by included/excluded/null date ranges

Question

First, a description of my task. I need to identify customers that have placed orders within the past 2 years. However, I need a subset of those records.

1. There needs to be 1 or more orders placed between 12-24 months ago.
2. A gap where NO orders are placed between 1-12 months ago.
3. 1 or more new orders have been placed within the past month.

Sounds easy enough, but I've spent way too much time isolating the constraints without receiving the desired output.

Here's my current code attempt:

SELECT * FROM
(SELECT CUSTOMER_ID AS "CUSTOMER", NAME, DATE_ENTERED,
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID
ORDER BY DATE_ENTERED desc) SEQ
FROM
A_ATEST
WHERE
DATE_ENTERED >= ADD_MONTHS(TRUNC(sysdate),-24) AND
(DATE_ENTERED >= ADD_MONTHS(TRUNC(sysdate),-1) AND
DATE_ENTERED < ADD_MONTHS(TRUNC(sysdate),-12)) AND
NOT EXISTS(SELECT null FROM A_ATEST WHERE
DATE_ENTERED < ADD_MONTHS(TRUNC(sysdate),-1) AND
DATE_ENTERED > ADD_MONTHS(TRUNC(sysdate),-12))
) a
  WHERE
  (SEQ = 1 AND
  DATE_ENTERED >= ADD_MONTHS(TRUNC(sysdate),-1)) AND
  (SEQ = 2 AND
  DATE_ENTERED < ADD_MONTHS(TRUNC(sysdate),-12))

SAMPLE DATA: (I don't see a way to add a table, so here goes...)

CUSTOMER, NAME, DATE_ENTERED
100       A     08-APR-20
100       A     01-MAR-20
100       A     01-MAR-20
101       B     09-MAR-20
101       B     07-MAR-19
101       B     01-MAR-19
102       C     04-APR-20
102       C     03-JAN-19
102       C     05-JAN-18

Ideally, the result set from my current code should display:

CUSTOMER, NAME, DATE_ENTERED, SEQ
102       C     04-APR-20     1
102       C     03-JAN-19     2

I'm not married to my code as it is. I'm hoping someone can lead me to a better way to approach this task.

Thanks!

-dougbert

Answer 1

I think this will give you what you want. Your question says you want the list of customers, but your output data suggests you want a list of orders from those customers.

SELECT CUSTOMER_ID AS "CUSTOMER", NAME, DATE_ENTERED,
  FROM A_ATEST a1
 WHERE a1.DATE_ENTERED >= ADD_MONTHS(TRUNC(sysdate),-24)
   AND EXISTS ( SELECT 1 FROM A_ATEST a3
                 WHERE a3.customer_id  = a1.customer_id
                   AND a3.DATE_ENTERED BETWEEN ADD_MONTHS(TRUNC(sysdate), -24)
                                           AND ADD_MONTHS(TRUNC(sysdate), -12))
   AND NOT EXISTS ( SELECT 1 FROM A_ATEST a2 
                     WHERE a2.customer_id  = a1.customer_id
                       AND DATE_ENTERED < ADD_MONTHS(TRUNC(sysdate), -1) 
                       AND DATE_ENTERED > ADD_MONTHS(TRUNC(sysdate), -12))
   AND EXISTS ( SELECT 1 FROM A_ATEST a4
                 WHERE a4.customer_id  = a1.customer_id
                   AND a4.DATE_ENTERED > ADD_MONTHS(TRUNC(sysdate), -12))

The key here is that your subqueries need to correlate customer_id back to the outermost A_ATEST table. The way you had it written basically meant "and there exists an order from any customer between 1 and 12 months ago".

Answer 2

You need orders in the last two years with a gap of one year. That suggests lag():

select a.*
from (select a.*,
             max(case when prev_de < add_months(date_entered, -12) then 1 else 0 end) over (partition by customer_id) as has_12month_gap
      from (select a.*,
                   lag(date_entered) over (partition by CUSTOMER_ID order by date_entered) as prev_de,
                   max(date_entered) over (partition by customer_id) as max_de
            from A_ATEST a
            where date_entered > add_months(sysdate, -24)
           ) a
     ) a
where max_de > add_months(sysdate, -1) and 
      has_12month_gap = 1;

EDIT:

The above brings back all the transactions. For the customers only, it is similar logic but a little simpler:

select customer
from (select a.*,
             lag(date_entered) over (partition by CUSTOMER_ID order by date_entered) as prev_de
      from A_ATEST a
      where date_entered > add_months(sysdate, -24)
     ) a
group by customer
where max(date_entered) > add_months(sysdate, -1) and 
      max(case when prev_de < add_months(date_entered, -12) then 1 else 0 end) = 1;

Answer 3

In case anyone references my question in the future, I wanted to share my final production solution. So, there a number of changes to get the output I required.

SELECT DISTINCT a1.CUSTOMER_NO AS "CUSTOMER", ci.NAME, MAX(a1.DATE_ENTERED) AS "ORDER DATE", a1.SALESMAN_CODE AS "SALESPERSON"
  FROM CUSTOMER_INFO ci LEFT JOIN CUSTOMER_ORDER a1 ON ci.CUSTOMER_ID = a1.CUSTOMER_NO
 WHERE a1.DATE_ENTERED >= ADD_MONTHS(TRUNC(sysdate), -1)
   AND EXISTS ( SELECT 1 FROM CUSTOMER_ORDER a3
                 WHERE a3.customer_no  = a1.customer_no
                   AND a3.DATE_ENTERED BETWEEN ADD_MONTHS(TRUNC(sysdate), -24)
                                           AND ADD_MONTHS(TRUNC(sysdate), -12))
   AND NOT EXISTS ( SELECT 1 FROM CUSTOMER_ORDER a2 
                     WHERE a2.customer_no  = a1.customer_no
                       AND DATE_ENTERED < ADD_MONTHS(TRUNC(sysdate), -1) 
                       AND DATE_ENTERED > ADD_MONTHS(TRUNC(sysdate), -12))
   AND EXISTS ( SELECT 1 FROM CUSTOMER_ORDER a4
                 WHERE a4.customer_no  = a1.customer_no
                   AND a4.DATE_ENTERED > ADD_MONTHS(TRUNC(sysdate), -24))
GROUP BY a1.CUSTOMER_NO, ci.NAME, a1.SALESMAN_CODE
ORDER BY a1.CUSTOMER_NO, "ORDER DATE"

Thanks again to both eaolson and Gordon Linoff for your help in getting me to where I needed to go.