Latest transaction if no recent prior transactions

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle and SQL Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to use the Oracle Database SQL Reference Manual How to use SQL Developer to run SQL statements How to work with the Oracle database home page How to use SQL*Plus in Oracle How to use SQL Developer to work with an Oracle database How to view and edit table column definitions How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.