EXPERT RESPONSE
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.
|
