Ask the Expert

Latest transaction if no recent prior transactions

I would like to select the latest customer record only if there are not any prior transactions for the previous 180 days. My sample data is like:

CustNum     InvDate
1200        2007-05-10
1200        2007-07-10
1200        2007-08-20
1000        2006-06-01
1000        2006-07-01
1000        2007-08-10

Assuming you are running this as of 2007-08-25, the query should return only one record of customer number 1000 with the invoice date of 2007-08-10. Any help is appreciated. Thanks in advance.

    Requires Free Membership to View

This is a variation of the "Latest X for each Y" scenario in the SQL FAQ: Common SQL questions. The wrinkle here is that an additional NOT EXISTS condition is required.

select CustNum
     , InvDate
  from Invoices as T
 where InvDate =
       ( select max(InvDate)
           from Invoices
          where CustNum = T.CustNum )
   and not exists
       ( select *
           from Invoices
          where CustNum = T.CustNum 
            and DateSubtract(T.InvDate,InvDate)
                <= 180 )

Here, DateSubtract is a function to calculate the difference, in days, between those two dates. This function is available in every database system, but it's different in each one, so you'll have to look it up.

Notice how each of the two subqueries uses a correlation variable, namely T. This correlates the rows being examined in the subquery with the row in the outer query, as identified by the table alias T for the Invoices table in the outer query. So T.InvDate is the InvDate of the row in the outer query, which may or may not be selected for the result set, depending on whether the outer query's WHERE clause is satisfied. The other Invdate in the subquery is the InvDate of every row in the table which has the same CustNum as T.Custnum, the row in the outer query.

It does sound a bit confusing, doesn't it.

Another way to do the second subquery is:

   and 0 =
       ( select count(*)
           from Invoices
          where CustNum = T.CustNum 
            and DateSubtract(T.InvDate,InvDate)
                <= 180 )

Compare this with the NOT EXISTS subquery.

This was first published in August 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: