How to write this one? - SQL Server Forums

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)

How to write this one?

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-04-06 : 09:28:18

Hi..
I have the following table
AccountNo Balance
--------- -------
1 0
110 50
120 25
12011 25

How can I write a select statement that will give me the Balance of accountno 1 to be 100..that is accoutns (110,120,12011) are sub accounts from acount no 1..so their balances will sum up in the main account no.
The account no column is a char(10)...maybe a grouping on the first cahracter..but how to do this?
Thanks In Advance

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-06 : 11:14:58

select sum(Balance), left(AccountNo,1)
from tbl
group by left(AccountNo,1)

An exceedingly bad table design and almost certainly wrong (as in not being able to hold consistent data).
Anyone want to guess the next question.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-04-07 : 01:52:34

lol..
first..this is just two columns of the table..so the design is not BAD as u thought it is!..i just needed that for specific report!.
hierarchy of accounts (logically) is implied in the CODING. that is it!
Second..it is not mine!..although I think it is a good design!..lol
so I shouldnt be taking credit for it..IF ANY!

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-04-07 : 04:30:59

For the first level it worked fine!!..that is LEFT(MainAccountNo,1)..but..for the second level this the query I am using :
SELECT LEFT(MainAccountNo,3) AccountNo, SUM(Balance) Balance
FROM Accounts A INNER JOIN AccountsTotals B
ON A.AccountId = B.AccountId
WHERE A.FiscalYear = 2003
Group By LEFT(MainAccountNo,3)

It is showing the following results :
AccountNo Balance
--------- --------
1 .000
110 25
115 .000
117 25
120 50
.
.
.etc...
What I WANT is the following :
AccountNo Balance
--------- --------
1 100
110 25
115 .000
117 25
120 50

note : account 1 actually has no balance but it is the SUM of any sub account below it in the tree...ofcourse account 110 is the same and so on!..
I would appreciate it if u can help!
Thanks Again

Edited by - bigretina on 04/07/2003 04:31:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources