Need help please

Question

User-1218295081 posted

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

 

AccountID	AccountName	ParentID	Level
1	x	null	0
2	x1	1	1
3	x11	2	2
4	x111	3	3
5	x112	3	3
6	x12	2	2
7	x121	6	3
8	x122	6	3
9	x123	6	3
10	x2	1	1
11	x21	10	2
12	x211	11	3
13	x2111	12	4
14	x2112	12	4
15	x2113	12	4
16	x22	10	2
17	x221	16	3
18	x222	16	3
19	x223	16	3
20	x3	1	1

 

and the second table called "AccountBalance" which has the following fields :

 

AccountID	AccountName	Debit	Credit
4	x111	10	10
5	x112	10	10
7	x121	0	15
8	x122	15	0
9	x123	20	30
13	x2111	10	0
14	x2112	20	30
15	x2113	50	40
17	x221	74	22
18	x222	50	50
19	x223	70	80

now i want to make a sql query to get sum of each level to give me this result

AccountID	AccountName	Debit	Credit
1	x	429	407
2	x1	55	65
3	x11	20	20
4	x111	10	10
5	x112	10	10
6	x12	35	45
7	x121	0	15
8	x122	15	0
9	x123	20	30
10	x2	324	282
11	x21	80	70
12	x211	80	70
13	x2111	10	0
14	x2112	20	30
15	x2113	50	40
16	x22	244	212
17	x221	74	22
18	x222	50	50
19	x223	70	80
20	x3	50	60

so how can i get this result

please help me to do this .....

thanks alot for help

Answer 1

User-2119480821 posted

start here: http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx 

Answer 2

User848370396 posted

declare @Accounts table
(
	AccountId int identity(1,1),
	AccountName varchar(100),
	ParentId int,
	Level int
)

insert into @Accounts
select 'x',null,0 union all
select 'x1',1,1 union all
select 'x11',2,2 union all
select 'x111',3,3 union all
select 'x112',3,3 union all
select 'x12',2,3 union all
select 'x121',6,3 union all
select 'x122',6,3 union all
select 'x123',6,3 union all
select 'x2',1,1 union all
select 'x21',10,2 union all
select 'x211',11,3 union all
select 'x2111',12,4 union all
select 'x2112',12,4 union all
select 'x2113',12,4 union all
select 'x22',10,2 union all
select 'x221',16,3 union all
select 'x222',16,3 union all
select 'x223',16,3 union all
select 'x3',1,1


declare @AccountBalance table
(
	AccountId int,
	AccountName varchar(100),
	Debit int,
	Credit int
)
insert into @AccountBalance
select 4,'x111',10,10 union all
select 5,'x112',10,10 union all
select 7,'x121',0,15 union all
select 8,'x122',15,0 union all
select 9,'x123',20,30 union all
select 13,'x2111',10,0 union all
select 14,'x2112',20,30 union all
select 15,'x2113',50,40 union all
select 17,'x221',74,22 union all
select 18,'x222',50,50 union all
select 19,'x223',70,80 


declare @MaxLevel int
set @MaxLevel = (select max(Level) from @Accounts)
declare @Output table
(
	Id int identity(1,1),
	AccountId int,
	AccountName varchar(100),
	ParentId int,
	Debit int,
	Credit int,
	Level int	
)
declare @CurrLevel int
set @CurrLevel = @MaxLevel
while(@CurrLevel >= 0)
begin
	if(@CurrLevel = @MaxLevel)	
		insert into @Output
		select AB.AccountId,AB.AccountName,ParentId,AB.Debit,AB.Credit,1 as [Level] from @AccountBalance AB
		inner join @Accounts A on AB.AccountId = A.AccountId 
	else
	begin
		insert into @Output	
		select AB.AccountId,AB.AccountName,AB.ParentId,sum(O.Debit),sum(O.Credit),o.[Level]+1  from @Output O
		inner join @Accounts AB on AB.AccountId = O.ParentId
		where AB.AccountId not in (select AccountId from @Output)
		group by AB.AccountId,AB.AccountName,AB.ParentId,O.[Level]
		
	end
	set @CurrLevel = @CurrLevel - 1
end

select * from @Output order by AccountName

 

Answer 3

User-1218295081 posted

thanks this soluation was good but it has 1 problem i can't order by name because it's first low performance second account name already exits in example isn't realy so i can't control that account names enterd orderd to display data in correct dir

Answer 4

User848370396 posted

its not a nice situation.. :( .

There is no way, this requirement can be done without using cursors/loops. Convert the above code into cursors, which are faster.

recursive cte has the disadvantage. we cant use group by in recursive part. (as u need the sum of childs)

If u change it to cursor based,  and if the table has proper indexes,it will be faster.

 

But another hurdle is of "Order by", the Debit-Credot calculation is down-up and order by is up-down. it needs 2 processing cycles(cursors or loops).