Brain is slow today
I have a SQL Server 2000 table with about 12 million records. These are individual transactions.
The way they work is each record has the last accummulated balance. Why? Because they refer to a smart card that has an embedded chip. The chip records the transaction number and the current balance.
I just got asked for a report on a certain group of cards (same Client_ID). They want to get the current balance of each card. We do not have such a report on the app they use as it was never required. We do have a program that queries one card and lists all their transactions, calculating the current balance. They do not want that this time. There is always a difference between calculated and chip balance as some transactions may not have been yet received here (e.g. a POS terminal has not 'closed' yet).
Task: I need to query SQL Server for the balance in the last transaction of each account. For simplicity say I have the following fields (actually it's much more complex and sample field names are not the real ones but will do for this example):
Transactions table:
Client_ID C(6)
Account C(16)
Tran_Counter N(5)
Amount N(10,2)
So I need to get the Amount for the MAX(Tran_Counter) for a client. I need ALL individual Accounts (i.e. no GROUP BY). Syntax should be T-SQL.
SELECT Account, Amount
FROM Transactions
WHERE Client_ID='123456'
...
...
I need it for MAX(Tran_Counter), so I think it must be some kind of correlated query? But I can't seem to get it right.
TIA