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.

There needs to be 1 or more orders placed between 12-24 months ago.
A gap where NO orders are placed between 1-12 months ago.
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

I've not tested the whole thing but it's looking for rows where `seq` is simultaneously `1` and `2`. You probably need an `or` instead of an `and`. — William Robertson, Apr 08 '20 at 19:17
Also I'm not sure `date_entered` can be both `>= add_months(trunc(sysdate), -1)` and `< add_months(trunc(sysdate), -12)`. — William Robertson, Apr 08 '20 at 19:24

score 0 · Answer 1 · answered Apr 08 '20 at 20:34

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;

Thanks Gordon, I went straight for the customers only code. BTW: I'm running my query via a remote connection to our IFS (ERP) Oracle server and using SQL Developer on the server. I only made slight modifications to your code, since I ran it on the production server. It hangs on Line 8 Column 1 which would be the WHERE clause. The error message states "ORA-00933: SQL command not properly ended". So, I assumed it didn't like the GROUP BY clause placed before the WHERE. Moving GROUP BY after that yielded the same error code. Any ideas? — dougbert, Apr 08 '20 at 21:57

eaolson · Accepted Answer · 2020-04-09T15:33:50.847

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

(2nd) WHERE a2.customer_id = a1.customer_id The a2 needed to be a3 (4th) WHERE a2.customer_id = a1.customer_id The a2 needed to be a4 Ran it and it provided the listing that allowed me to positively identify each of the customers that met the criteria, which is what I needed to start with. Ultimately, I'll refine your code for how I'll use this in production. I really appreciate you time and effort and the additional comments about where I strayed. — dougbert, Apr 08 '20 at 22:37
Sorry, cut and paste fail. I think I've corrected the query for posterity. — eaolson, Apr 09 '20 at 15:34

score 0 · Answer 3 · answered Apr 09 '20 at 14:50

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.

Limiting records by included/excluded/null date ranges

3 Answers3