Drill down query Accounts - 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)

Drill down query Accounts

Author

Topic

sfunds
Starting Member

7 Posts

Posted - 2005-10-12 : 06:11:38

Hi,
I have a table in which I store Names of ACCount

id /account id unique
name name of account
pid //parent of the account

The parent of account allows drill down of accounts
The table looks like this
1 Assets 0
2 Liabilities 0
3 Capital 0
4 income 0
5 expenses 0

7 fixed 1
8 computers 7
9 desktop 8
10 Laptop 8
11 mainframe 8

12 currentAssets 1
13 Cash in Hand 12
14 Bank 12
15 HDFC 14
16 ICICI 14
17 SBI 14
18 UTIBank 14

103 Telephones 5
104 BSNL 103
105 AirTell 103

and so on

I have another table which stores all transaction

debitid
creditid
amount
voucherno
comments

debitid and creditid or id of the first table ie account id

18 13 10000 1045A deposited amount from hand into uti bank
10 17 29890 1356A Purchased Laptop for MD

105 18 3635 1033B paid Telephone bills vide cheque #312456

and so on
what I want

For each account i want the balance
The parent account should be sum of all its child
some accounts like Bank will have debit entries as well as credit entries
The sum can be debit sum and credit sum seprately or combined for each account

what is the best way to do the query

sample of above out put
(Not from above table)
id , name, debit, credit
7 Assets 285230 41230
13 CASH 20000 10000
14 BANK 265230 31230
17 SBI 160000 6000
18 UTIbank 24000 4000
15 HDFC 21230 1230
16 ICICI 60000 20000

Basically i want a drill down query

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-12 : 16:38:24

Something like this should get you started...


SELECT a.AccountID, a.AccountName, SUM(a.DebitAmount), SUM(a.CreditAmount)
FROM
	(
		SELECT
			debitid as AccountID,
			t2.[name] as AccountName,
			amount as DebitAmount,
			0 as CreditAmount
		FROM
			transactiontable t1
			JOIN account t2 ON t1.debitid = t2.[id]
	
		UNION ALL
	
		SELECT
			creditid as AccountID,
			t2.[name] as AccountName,
			0 as DebitAmount,
			amount as CreditAmount
		FROM
			transactiontable t1
			JOIN account t2 ON t1.debitid = t2.[id]
	) a
GROUP BY 
	AccountID, 
	AccountName

---------------------------
EmeraldCityDomains.com

sfunds
Starting Member

7 Posts

Posted - 2005-10-13 : 01:48:23

hi,Thanks for reply.

This query gives total for all child nodes.
I have a parent child relation ship as mentioned
I wanted the totals for all parents at different level
Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-13 : 14:16:04

Well, like I said, that will get you started. Here are a couple more tips: Research the WITH ROLLUP option of the GROUP BY clause, and read up on how to handle trees and hierarchies in SQL.

---------------------------
EmeraldCityDomains.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 01:51:10

More Trees
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources