August 27, 2004 at 8:53 am
I have been tasked with querying one of the "payment" tables in our database to pull the most recent payment made to each account.
The table is arranged like this:
ID Account # Description Date Amt
1 123456 Gas Payment 5/8/04 20.56
2 123457 Deposit Payment 5/19/04 60.59
3 123456 Gas Payment 8/9/04 89.54
August 27, 2004 at 10:16 am
Hi,
select the most recent payment that time thorugh date u can select this
select * from payment where date between '2004-08-27' to '2004-08-20'
August 27, 2004 at 10:19 am
That option will not really work since last payment dates range from 2002-present. We have accounts that have not made payment in a LONG time.
August 27, 2004 at 10:21 am
SELECT [ACCOUNT #], MAX([DATE]) FROM PAYMENT_TABLE GROUP BY [ACCOUNT #]
That would get you a result like the following for the above data:
123456 8/9/04
123457 5/19/04
If you need the Description, you can add that to both the select and group by list, however you'll get a record for each combination of account # and description.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply