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