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".