Q

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. How do I do this in SQL?

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.

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close