Need help to make this sql query

Question

Post reply

Need help to make this sql query

noor3rb

Grasshopper

Points: 10
More actions
May 15, 2010 at 1:52 am

#235057

Hi everyone , i need help to do this query as i have two tables in database
1- First Table called "Accounts" which have the following fields
AccountIDAccountNameParentIDLevel
1x null0
2x111
3x1122
4x11133
5x11233
6x1222
7x12163
8x12263
9x12363
10x211
11x21102
12x211113
13x2111124
14x2112124
15x2113124
16x22102
17x221163
18x222163
19x223163
20x311
and the second table called "AccountBalance" which has the following fields :
AccountIDAccountNameDebitCredit
4x1111010
5x1121010
7x121015
8x122150
9x1232030
13x2111100
14x21122030
15x21135040
17x2217422
18x2225050
19x2237080
now i want to make a sql query to get sum of each level to give me this result
AccountIDAccountNameDebitCredit
1x429407
2x15565
3x112020
4x1111010
5x1121010
6x123545
7x121015
8x122150
9x1232030
10x2324282
11x218070
12x2118070
13x2111100
14x21122030
15x21135040
16x22244212
17x2217422
18x2225050
19x2237080
20x35060
so how can i get this result
please help me to do this .....
thanks alot for help

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

First thing to notie: normalize your table.

There is no need to put AccountName in your Accounts table.

In order to get the hierarchy tree you should try to follow the example given in BOL (BooksOnLine, the SQL Server help system), section "recursive CTEs [SQL Server]", example D.

If you need further assistance, please provide ready to use sample data together with what you've tried so far as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 2

noor3rb (5/15/2010)
Hi everyone , i need help to do this query as i have two tables in database
1- First Table called "Accounts" which have the following fields
AccountIDAccountNameParentIDLevel
1x null0
2x111
3x1122
4x11133
5x11233
6x1222
7x12163
8x12263
9x12363
10x211
11x21102
12x211113
13x2111124
14x2112124
15x2113124
16x22102
17x221163
18x222163
19x223163
20x311
and the second table called "AccountBalance" which has the following fields :
AccountIDAccountNameDebitCredit
4x1111010
5x1121010
7x121015
8x122150
9x1232030
13x2111100
14x21122030
15x21135040
17x2217422
18x2225050
19x2237080
now i want to make a sql query to get sum of each level to give me this result
AccountIDAccountNameDebitCredit
1x429407
2x15565
3x112020
4x1111010
5x1121010
6x123545
7x121015
8x122150
9x1232030
10x2324282
11x218070
12x2118070
13x2111100
14x21122030
15x21135040
16x22244212
17x2217422
18x2225050
19x2237080
20x35060
so how can i get this result
please help me to do this .....
thanks alot for help

I just have to ask... did you ever get this worked out? If you did, any chance of you posting the code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)