Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    16

    Unanswered: Customers with no activity before a date

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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')
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    Last edited by Peter.Vanroose; 08-25-06 at 15:26.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    May 2006
    Posts
    16
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •