Q
Problem solve Get help with specific problems with your technologies, process and projects.

Latest transaction before a specified date

I have a Transactions table that stores card transaction data. TransactionID is a primary key that gets incremented with every transaction. Transactions don't occur every day. The Balance is adjusted with every transaction and reflected in the corresponding column. I need to find the latest transaction for the card before a specified date. How do I do that using a SQL statement?

I have a Transactions table that stores card transaction data. It has the following columns: TransactionID, TransDatetime,...

CardNumber, Balance. TransactionID is a primary key that gets incremented with every transaction. Transactions don't occur every day. The Balance is adjusted with every transaction and reflected in the corresponding column. I need to find the latest transaction for the card before a specified date. How do I do that using a SQL statement?

The method which is easiest to understand (for me, anyway) is the correlated subquery.

select TransactionID
     , TransDatetime
     , CardNumber
     , Balance
  from Transactions as T
 where TransDatetime =
     ( select max(TransDatetime)
         from Transactions
        where CardNumber = T.CardNumber
          and TransDatetime < '2006-10-01 09:37:00' )

Each row in the outer query is evaluated based on the WHERE clause. If the WHERE clause evaluates TRUE, then that row is placed into the query result set. The subquery is correlated because in order to evaluate it, it needs to use the value of T.CardNumber, which is the value from the row in the outer query. Read the WHERE clause like a normal English sentence a few times, and it will soon make sense. In fact, it comes very close to matching the original requirement ("the latest transaction for the card before a specified date").

Another way to do it is with a join.

select T.TransactionID
     , T.TransDatetime
     , T.CardNumber
     , T.Balance
  from Transactions as T
inner
  join (
       select CardNumber
            , max(TransDatetime) as maxDateTime
         from Transactions
        where TransDatetime < '2006-10-01 09:37:00'
       group
           by CardNumber 
       ) as M
    on M.CardNumber = T.CardNumber
   and M.maxDateTime = T.TransDatetime  

In this query, the maximum datetimes are in a separate table, a so-called derived table (because it is derived as the result of a query). The derived table contains the maximum TransDatetime for each CardNumber. Note that the join is on two columns.

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close