problem getting SELECT statement right

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem getting SELECT statement right

Author

Topic

cathal
Starting Member

4 Posts

Posted - 2002-07-16 : 12:11:48

Hello,
I'm using sql 6.5 and I have to select a number of account balances (acct_bal) out of a table (tblacct_bal) based on account number (acct_no). However the account balances are only written to this table when the account is updated, so I have a number of accounts that are only updated every few days. I need to write a query to obtain the current balance for each account and on what day it was last updated. It's probably simple, but I've been looking at it now for so long I can't see the solution.

I could fire it all into a stored procedure and pull out the account numbers into a cursor and then select each record out (using set rowcount 1 for a TOP style of query based on ascending balance dates), but as I have to include the query in a crystal report I would prefer to solve it in a sql statement. Any help would be appreciated.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-16 : 12:19:18

Is there a datatime field in your table that keeps track when it was updated/insert?

-----------------------
Take my advice, I dare ya

cathal
Starting Member

4 Posts

Posted - 2002-07-16 : 12:31:04

Sorry, yes there is an acct_bal_date field which is marked with the date updated. A sample of data would look like:

acct_no acct_bal acct_bal_date
1 50 16/07/2002
1 25 15/07/2002
1 35 14/07/2002
2 100 11/07/2002
3 200 16/07/2002
3 200 02/06/2002

And I need the latest acct_bal of each account

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-16 : 13:03:17

Try this and let me know if it doesn't work.

Select a.acct_no, a.acct_bal, a.acct_bal_date
From tblacct_bal a INNER JOIN
(Select acct_no, max(acct_bal_date) acct_bal_date
FROM tblacct_bal
Group by acct_no) b
ON a.acct_no = b.acct_no
AND a.acct_bal_date = b.acct_bal_date

Jeremy

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-16 : 13:05:04

my solution in the link below....should provide you with guidance on how to solve this.....should be just a matter of mapping your tables/columns to the ones listed....both problems seem to be similiar

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=17644

cathal
Starting Member

4 Posts

Posted - 2002-07-16 : 13:14:50

Thanks gents, a combination of the two has got the right results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources