Customers with no activity before a date

arlf · #1 (**permalink**) 08-25-06, 11:04

DB2 v7.2 OS/390
I am looking the best way to solve the next requirement.
There is a table (TB1) which includes, amonst others, the columns CUST_ID (customer id) and PAY_DT (payment date).
The query must select the customers with payments after a control date (ie 2006-01-01) and no payments before this date. In others words, the users that did payments before the control date must be rejected, in spite of having payments after the date given.

Thanks in advance, ARLF.

Wim · #2 (**permalink**) 08-25-06, 11:51

Code:

SELECT DISTINCT TBL.CUST_ID
FROM TBL
WHERE PAY_DT > '2006-01-01' AND
NOT EXISTS (SELECT 1
            FROM TBL as TBL2
            WHERE TBL2.CUST_ID = TBL.CUST_ID AND
                  TBL2.PAY_DT <= '2006-01-01')

Peter.Vanroose · #3 (**permalink**) 08-25-06, 14:11

Wim, your inner query "TBL" is referring the inner table, not the outer one, so TBL2.cust_id = TBL.cust_id is trivially satisfied.
So you need

Code:

SELECT DISTINCT CUST_ID
FROM   TBL AS TBL1
WHERE  PAY_DT >= '2006-01-01'
  AND  NOT EXISTS (SELECT 1
                   FROM   TBL
                   WHERE  CUST_ID = TBL1.CUST_ID
                     AND  PAY_DT < '2006-01-01')

The following is an alternative solution: it gives only the customers for which the first payment date is after a given date:

Code:

SELECT cust_id
FROM   tbl
GROUP BY cust_id
HAVING min(pay_dt) >= '2006-01-01'

Not necessarily more performant than the first solution, unless there is an index on the column pair (cust_id,pay_dt).
Otherwise it depends on the number of different customers and the number of entries per customer.
E.g., if there are relatively few payments after the reference date (say 1% of the table) the first solution will run faster.

arlf · #4 (**permalink**) 08-25-06, 19:35

Thanks a lot.
To simplify, the table has an index on column CUST_ID and other on PAY_DT.
I ran the 2 queries with a little modification for the second ( [ where PAY_DT >= '2000-01-01' ], last 5 years). In spite there are relatively few payments after the control date (about 0.5 %), this solution ran very faster (10 mins vs 80 mins). However, I will keep in mind the 2 queries and Peter comments.
Thanks again. ARLF

Internet Services

Web Development

Digital Marketing

Consumer Tech