locked
Need help please RRS feed

  • 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

    Saturday, May 15, 2010 3:26 AM

Answers

  • 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


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 17, 2010 12:27 AM
  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 17, 2010 2:38 AM

All replies

  • Saturday, May 15, 2010 5:46 AM
  • 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


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 17, 2010 12:27 AM
  • 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

    Monday, May 17, 2010 2:16 AM
  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 17, 2010 2:38 AM