I have a file with customer account transactions. Each customer account may have none, one, or more payment transactions, as well as other transactions, in the file. I'm interested in retrieving just the latest, i.e. most recent, payment transaction for each customer account, and I'll be using this data in a CTE as part of a more complex SQL statement.
Here's a simple SQL to retrieve the account and payment date and amount for the payment transactions:
Since each account can have multiple payment transactions but I only want the most recent one, I think maybe I need to group these by account and transaction type and then limit it to somehow only select the most recent date... maybe sort by date in descending order and then select just one for each customer?
What is the best way to accomplish this? Thanks.
Here's a simple SQL to retrieve the account and payment date and amount for the payment transactions:
Code:
[FONT=courier new]select Acct, (TranYY * 10000 + TranMM * 100 + TranDD) as PmtDate, TranAmt as PmtAmt from MyFile where TranTyp = 'P'[/FONT]
What is the best way to accomplish this? Thanks.
Comment